Need output Of Report

Analyst_SQL 3,551 Reputation points
2022-04-08T18:34:52.013+00:00

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

191462-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,107 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ronen Ariely 15,091 Reputation points
    2022-04-08T19:29:03.933+00:00

    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  
    

    191436-image.png

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. amomen 381 Reputation points
    2022-04-08T19:21:37.757+00:00

    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.

    2 people found this answer helpful.