question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked EchoLiu-msft edited

Display all item from Itemmaster table

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

136011-image.png


sql-server-generalsql-server-transact-sql
image.png (13.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Is the screenshot you provided your expected output? How is Opening calculated?

0 Votes 0 ·

No,I said ,I want to display all description from itemmasterfile where column I_Hot ='Hot' in my output,where there codeitem exist in #Probale table or not.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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:
136474-image.png


Regards
Echo



image.png (21.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered akhterhussain-3167 commented

Hi @akhterhussain-3167,

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:
136178-image.png


136144-image.png

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.



image.png (19.1 KiB)
image.png (18.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@EchoLiu-msft
I want below output,means that if QTY is 0 then Target_qty and Percentage must be calculate and do not display null
136344-image.png


0 Votes 0 ·
image.png (19.4 KiB)