Need output Of Report

Question

Below is data

Create Table #itemmasterfile(CodeItem int,Weight  int,Descriptionitem varchar(50),Y_per Decimal(10,2),I_Hot varchar(50),E_Date date)   
    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,'2022-04-01',5000,null)  
         
    Insert INTO #tbl_Issuance_Rags values (2,'2022-04-02',2500,null)  
         
    Insert INTO #tbl_Issuance_Rags values (3,'2022-04-05',5000,null)  
         
    Insert INTO #tbl_Issuance_Rags values (4,'2022-04-08',5000,null)  
      
 INSERT INTO #itemmasterfile VALUES(1,11,'Adult Sweater',0.30,'Hot','2021-04-01')   
    INSERT INTO #itemmasterfile VALUES(2,11,'HAEAVY SS',0.25,'Hot','2021-04-02')    
    INSERT INTO #itemmasterfile VALUES(3,12,'LADIES MIX BLOUSES',0.37,'Hot','2021-04-08')   
    INSERT INTO #itemmasterfile VALUES(4,11,'LAPU',0.15,'Hot','2022-04-08')    
    INSERT INTO #itemmasterfile VALUES(5,13,'LAPU TROUSER',0.30,'Hot','2022-04-09')    
    INSERT INTO #itemmasterfile VALUES(6,13,'LL #2',0.45,'Hot','2022-04-10')    
    INSERT INTO #itemmasterfile VALUES(7,12,'N4S MXT',0.31,'Hot','2022-04-11')    
    INSERT INTO #itemmasterfile VALUES(8,11,'Over Coat',0.25,'Hot','2023-04-11')    
  
  
	         INSERT INTO #Probale VALUES(10000,1,270,1,'2022-04-01',null,1)   
    INSERT INTO #Probale VALUES(10001,2,270,1,'2022-04-01',null,1)    
    INSERT INTO #Probale VALUES(10002,3,270,1,'2022-04-01',null,1)   
    INSERT INTO #Probale VALUES(10003,4,270,1,'2022-04-03',null,1)    
    INSERT INTO #Probale VALUES(10004,5,270,1,'2022-04-04',null,1)    
    INSERT INTO #Probale VALUES(10005,6,270,1,'2022-04-04',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)  
  

QTY is from Probale table
R_Weight from #tbl_Issuance_Rags

Answer 1

Hi,

What is the relation between the #tbl_Issuance_Rags and the other tables?

Why the sum of all the rows in #tbl_Issuance_Rags related to the Adult Sweater row in the result set?

Getting the first two columns in the result set is clear to me but not the third column

select i.Descriptionitem, SUM(ISNULL(p.prdqty,0))  
from #itemmasterfile i  
LEFT JOIN #Probale p on i.CodeItem = p.CodeItem  
GROUP BY i.Descriptionitem  
GO  

Answer 2

Hi.

You missed a column for #tbl_Issuance_Rags which contains CodeItem. I added it. Here is the code:

ALTER TABLE #tbl_Issuance_Rags ADD CodeItem INT
go
UPDATE #tbl_Issuance_Rags SET CodeItem = 1

SELECT mf.CodeItem,mf.Descriptionitem Item,COUNT(p.CodeItem)/COUNT(ISNULL(id,1)) Qty, ISNULL(CONVERT(VARCHAR(50),SUM(r.R_Weight)),'') R_Weight
FROM #itemmasterfile mf left JOIN #Probale p
ON p.CodeItem = mf.CodeItem
LEFT JOIN #tbl_Issuance_Rags r
ON mf.CodeItem=r.CodeItem
GROUP BY mf.CodeItem,mf.Descriptionitem

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.