Display all item from Itemmaster table

Question

I want to display all item form itemmaster table where I_Hot is equal to Hot

Below is Data,

 Create Table #itemmasterfile(CodeItem int,Weight  int,Descriptionitem varchar(50),Y_per Decimal(10,2),I_Hot varchar(50))   
   CREATE TABLE #Probale (BID INT,CodeItem int,weight int,prdqty int,EntryDate date,DelID int,FID int)   
   Create table #tbl_Issuance_Rags (ID int,R_Date date,R_Weight int,Delid int)  
  
  
   Insert INTO #tbl_Issuance_Rags values (1,'2021-09-28',5000,null)  
     
   Insert INTO #tbl_Issuance_Rags values (2,'2021-09-28',2500,null)  
     
   Insert INTO #tbl_Issuance_Rags values (3,'2021-09-28',5000,null)  
     
   Insert INTO #tbl_Issuance_Rags values (4,'2021-09-28',5000,null)  
  
INSERT INTO #itemmasterfile VALUES(1,11,'Adult Sweater',0.30,'Hot')   
   INSERT INTO #itemmasterfile VALUES(2,11,'HAEAVY SS',0.25,'Hot')    
   INSERT INTO #itemmasterfile VALUES(3,12,'LADIES MIX BLOUSES',0.37,'Hot')   
   INSERT INTO #itemmasterfile VALUES(4,11,'LAPU',0.15,'Hot')    
   INSERT INTO #itemmasterfile VALUES(5,13,'LAPU TROUSER',0.30,'Hot')    
   INSERT INTO #itemmasterfile VALUES(6,13,'LL #2',0.45,'Hot')    
   INSERT INTO #itemmasterfile VALUES(7,12,'N4S MXT',0.31,'Hot')    
   INSERT INTO #itemmasterfile VALUES(8,11,'Over Coat',0.25,'Hot')    
   INSERT INTO #itemmasterfile VALUES(9,12,'ST MIX T SHIRTS',0.19,'Hot')   
   INSERT INTO #itemmasterfile VALUES(10,13,'W / WIPER ',0.11,'Hot')    
   INSERT INTO #itemmasterfile VALUES(11,13,'WHITE PANTS',0.41,'Hot')   
      INSERT INTO #itemmasterfile VALUES(12,13,'WHITE PANTS',null,null)    
  
  
  
  
	    INSERT INTO #Probale VALUES(10000,1,270,1,'2021-09-28',null,1)   
   INSERT INTO #Probale VALUES(10001,2,270,1,'2021-09-28',null,1)    
   INSERT INTO #Probale VALUES(10002,3,270,1,'2021-09-28',null,1)   
   INSERT INTO #Probale VALUES(10003,4,270,1,'2021-09-28',null,1)    
   INSERT INTO #Probale VALUES(10004,5,270,1,'2021-09-28',null,1)    
   INSERT INTO #Probale VALUES(10005,6,270,1,'2021-09-28',null,1)  
     INSERT INTO #Probale VALUES(10007,6,270,1,'2021-09-28',null,2)  
      INSERT INTO #Probale VALUES(10008,6,270,1,'2021-09-28',null,2)  
	    
  
  
	     
 ;with I as ( Select CodeItem, Descriptionitem,  Y_per,weight from #itemmasterfile where I_Hot='Hot')  
   
 ,B as (  
 select I.Descriptionitem,sum(isnull(b.prdqty,0)) QTY,sum(isnull(I.weight,0)) Bweight, b.FID ,I.Y_per as targets  
 from #Probale b  
 inner     join I  on i.Codeitem=b.Codeitem   
  
   
 where b.EntryDate between '2021-09-28' and '2021-09-28' and b.DelID is null and b.FID=1   
 group by b.FID,Descriptionitem,I.Y_per)  
 ,C as (  
 select a.FID,sum(a.R_Weight) iWeight,R_Date EntryDate  
 from  tbl_Issuance_Rags a   
  
 where R_Date between  '2021-09-28' and '2021-09-28' and a.Delid is null and a.FID=1 and   a.R_Belt<'UK' and a.R_Dis_ID is null  
 group by a.FID,R_Date)  
 ,d as (  
  
 select upper(b.Descriptionitem) item,isnull(B.QTY,0)QTY  
 ,cast(     cast(B.Bweight as float)/cast(C.iWeight as float)*100 as decimal(10,2)) AS [Percentage],  
 targets,B.Bweight as Weight,C.iWeight as Opening  
 from B  
 inner join C on B.FID=C.FID)  
  
 select  item,QTY,ceiling(cast((((Opening*targets))/100)/100  as decimal(10,2)))as Target_QTY,Percentage,Weight,Opening from d

Current Result is coming.

Below

Accepted Answer

Please try:

 ;with I as   
 (Select CodeItem, Descriptionitem,  Y_per,weight  
 from #itemmasterfile where I_Hot='Hot')  
 ,B as (  
   select I.Descriptionitem,sum(isnull(b.prdqty,0)) QTY,  
   sum(isnull(I.weight,0)) Bweight,CASE WHEN b.FID IS NULL THEN 1 ELSE   
   b.FID END FID,I.Y_per as targets  
   from #Probale b  
   RIGHT join I on i.Codeitem=b.Codeitem   
   AND b.EntryDate between '2021-09-28' and '2021-09-28'   
   and b.DelID is null and b.FID=1   
   group by b.FID,Descriptionitem,I.Y_per)  
 ,C as (  
   select a.ID,sum(a.R_Weight) iWeight,R_Date EntryDate  
   from  #tbl_Issuance_Rags a   
   where R_Date between  '2021-09-28' and '2021-09-28'  
   and a.Delid is null and a.ID=1 --and a.R_Belt<'UK'   
   --and a.R_Dis_ID is null  
   group by a.ID,R_Date)  
 ,d as (  
   select upper(b.Descriptionitem) item,isnull(B.QTY,0)QTY  
   ,cast(cast(B.Bweight as float)/cast(C.iWeight as float)*100   
   as decimal(10,2)) AS [Percentage],  
   targets,B.Bweight as Weight,C.iWeight as Opening  
   from B  
   LEFT join C on B.FID=C.ID)  
  
 select  item,QTY,ceiling(cast((((Opening*targets))/100)--/100    
 as decimal(10,2))) as Target_QTY,[Percentage],[Weight],Opening,targets  
 from d

Output:

Regards
Echo

Answer

Hi @Analyst_SQL ,

Please check:

;with I as   
(Select CodeItem, Descriptionitem,  Y_per,weight  
from #itemmasterfile where I_Hot='Hot')  
,B as (  
  select I.Descriptionitem,sum(isnull(b.prdqty,0)) QTY,  
  sum(isnull(I.weight,0)) Bweight, b.FID ,I.Y_per as targets  
  from #Probale b  
  RIGHT join I on i.Codeitem=b.Codeitem   
  AND b.EntryDate between '2021-09-28' and '2021-09-28'   
  and b.DelID is null and b.FID=1   
  group by b.FID,Descriptionitem,I.Y_per)  
,C as (  
  select a.ID,sum(a.R_Weight) iWeight,R_Date EntryDate  
  from  #tbl_Issuance_Rags a   
  where R_Date between  '2021-09-28' and '2021-09-28'  
  and a.Delid is null and a.ID=1 --and a.R_Belt<'UK'   
  --and a.R_Dis_ID is null  
  group by a.ID,R_Date)  
,d as (  
  select upper(b.Descriptionitem) item,isnull(B.QTY,0)QTY  
  ,cast(cast(B.Bweight as float)/cast(C.iWeight as float)*100   
  as decimal(10,2)) AS [Percentage],  
  targets,B.Bweight as Weight,C.iWeight as Opening  
  from B  
  LEFT join C on B.FID=C.ID)  
   
select  item,QTY,ceiling(cast((((Opening*targets))/100)--/100    
as decimal(10,2))) as Target_QTY,[Percentage],[Weight],Opening   
from d

Output:

I commented out a.R_Belt and a.R_Dis_ID because there are no two columns in the sample data you provided.

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

Display all item from Itemmaster table

1 additional answer