Stock query Require

Question

Stock query Require

Analyst_SQL 3,576

Below is data and output is require ,

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))    
     
CREATE TABLE #Bigbaleprd(BID INT,CodeItem INT,prdQTY INT,Bweight int,Entrydate DATETIME)    
     
CREATE TABLE #DispatchM(DID INT, Entrydate DATETIME)    
CREATE TABLE #DispatachD(ID INT,DID int,BID int,Codeitem int,prdQTY int,Bweight int,)    
        
INSERT INTO #ItemMasterFile VALUES(1,'A')    
INSERT INTO #ItemMasterFile VALUES(2,'B')    
INSERT INTO #ItemMasterFile VALUES(3,'C')    
INSERT INTO #ItemMasterFile VALUES(4,'D')    
INSERT INTO #ItemMasterFile VALUES(5,'e')    
INSERT INTO #ItemMasterFile VALUES(6,'f')    
INSERT INTO #ItemMasterFile VALUES(7,'g')    
INSERT INTO #ItemMasterFile VALUES(8,'h')    
INSERT INTO #ItemMasterFile VALUES(9,'K')    
INSERT INTO #ItemMasterFile VALUES(10,'L')    
INSERT INTO #ItemMasterFile VALUES(11,'M')    
     
  
INSERT INTO #Bigbaleprd VALUES(10006,3,1,400,'07-02-2022')    
INSERT INTO #Bigbaleprd VALUES(10007,1,1,300,'07-02-2022')    
INSERT INTO #Bigbaleprd VALUES(10008,3,1,200,'07-02-2022')    
INSERT INTO #Bigbaleprd VALUES(10009,11,1,150,'07-02-2022')     
INSERT INTO #Bigbaleprd VALUES(10010,8,1,100,'07-02-2022')     
INSERT INTO #Bigbaleprd VALUES(10011,1,1,300,'08-02-2022')    
INSERT INTO #Bigbaleprd VALUES(10012,3,1,200,'08-02-2022')    
INSERT INTO #Bigbaleprd VALUES(10013,11,1,150,'08-02-2022')    
INSERT INTO #Bigbaleprd VALUES(10014,10,1,250,'08-02-2022')    
INSERT INTO #Bigbaleprd VALUES(10018,10,1,50,'08-02-2022')    
INSERT INTO #Bigbaleprd VALUES(10015,8,1,900,'09-02-2022')    
INSERT INTO #Bigbaleprd VALUES(10016,9,1,800,'09-02-2022')    
INSERT INTO #Bigbaleprd VALUES(10017,9,1,600,'09-02-2022')    
     
INSERT INTO #DispatchM VALUES(1001,'08-02-2022')    
INSERT INTO #DispatchM VALUES(1002,'09-02-2022')    
     
INSERT INTO #DispatachD VALUES(1,1001,10011,1,1,300)    
INSERT INTO #DispatachD VALUES(2,1001,10012,3,1,200)    
INSERT INTO #DispatachD VALUES(3,1001,10013,11,1,150)    
INSERT INTO #DispatachD VALUES(4,1001,10014,10,1,250)    
INSERT INTO #DispatachD VALUES(5,1002,10015,8,1,900)    
INSERT INTO #DispatachD VALUES(6,1002,10016,9,1,800)    
INSERT INTO #DispatachD VALUES(7,1002,10017,9,1,600)

Note :

I am retrieving data from database on Date between 08/02/2022 to 08/02/2022 ,
Column calculation detail below

Opening_QTY/Opening_Weight from database before 08/02/2022,

IN_QTY/IN_Weight from #Bigbaleprd table ,from Date between 08/02/2022 to 08/02/2022 ,

Out_Qty/Out_Weight from #DispatchM and #DispatachD table , from Date between 08/02/2022 to 08/02/2022 ,

Closing_Qty (Opening_QTY+IN_QTY-Out_QTY) from Date between 08/02/2022 to 08/02/2022 ,

Closing_Weight (Opening_Weight + IN_Weight - OutWeight) from Date between 08/02/2022 to 08/02/2022 ,

4 answers

Your answer

Answer 1

Hi @Analyst_SQL
Please check this:

;WITH CTE1 AS  
(SELECT I.CodeItem  ,SUM(prdQTY)AS Opening_Qty,SUM(Bweight)AS Opening_Weight   
 FROM #ItemMasterFile I LEFT JOIN #Bigbaleprd B ON I.CodeItem=B.CodeItem  
 WHERE Entrydate<'08/02/2022'  
 GROUP BY I.CodeItem,Descriptionitem  
),CTE2 AS  
(SELECT I.CodeItem ,SUM(prdQTY)AS In_Qty,SUM(Bweight)AS In_Weight   
 FROM #ItemMasterFile I LEFT JOIN #Bigbaleprd B ON I.CodeItem=B.CodeItem  
 WHERE Entrydate BETWEEN '08/02/2022'AND '08/02/2022'  
 GROUP BY I.CodeItem,Descriptionitem  
),CTE3 AS  
(SELECT D.CodeItem,SUM(prdQTY)AS Out_Qty,SUM(Bweight)AS Out_Weight   
 FROM #DispatchM M LEFT JOIN #DispatachD D ON M.DID=D.DID  
                   LEFT JOIN #ItemMasterFile I ON I.CodeItem= D.Codeitem  
 WHERE Entrydate BETWEEN '08/02/2022'AND '08/02/2022'  
 GROUP BY D.CodeItem,Descriptionitem  
)  
SELECT UPPER(I.Descriptionitem) AS Item,ISNULL(Opening_Qty,0)AS Opening_Qty,ISNULL(Opening_Weight,0)AS Opening_Weight,  
       ISNULL(In_Qty,0)AS In_Qty,ISNULL(In_Weight,0)AS In_Weight,ISNULL(Out_Qty,0)AS Out_Qty,ISNULL(Out_Weight,0)AS Out_Weight,  
       ISNULL(Opening_QTY,0)+ISNULL(IN_QTY,0)-ISNULL(Out_QTY,0) AS Closing_Qty,  
       ISNULL(Opening_Weight,0) + ISNULL(IN_Weight,0) - ISNULL(Out_Weight,0) AS Closing_Weight    
FROM #ItemMasterFile I LEFT JOIN CTE1 C1 ON I.CodeItem =C1.CodeItem  
                       LEFT JOIN CTE2 C2 ON I.CodeItem =C2.CodeItem  
                       LEFT JOIN CTE3 C3 ON I.CodeItem =C3.CodeItem

Output:

Best regards,
LiHong

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.

LiHong-MSFT 10,056 Reputation points

2022-02-10T07:52:55.16+00:00

Note that the Closing_Qty of Item C should be 2 (Opening_QTY+IN_QTY-Out_QTY),it should not be 1(which is showned in your output picture)
Analyst_SQL 3,576 Reputation points

2022-02-11T10:19:24.483+00:00

I share new Screen Shot Below,Opening qty and Opening_Weight is not properly calculating ,means that Yesterday closing will be today Opening

Answer 2

Hi @LiHong-MSFT

Please Check below, that Item A Opening and closing Qty must be 1 not 2,you may check result with current data below.

 CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))    
         
 CREATE TABLE #Bigbaleprd(BID INT,CodeItem INT,prdQTY INT,Bweight int,Entrydate DATETIME)    
         
 CREATE TABLE #DispatchM(DID INT, Entrydate DATETIME)    
 CREATE TABLE #DispatachD(ID INT,DID int,BID int,Codeitem int,prdQTY int,Bweight int,)    
            
 INSERT INTO #ItemMasterFile VALUES(1,'A')    
 INSERT INTO #ItemMasterFile VALUES(2,'B')    
 INSERT INTO #ItemMasterFile VALUES(3,'C')    
 INSERT INTO #ItemMasterFile VALUES(4,'D')    
 INSERT INTO #ItemMasterFile VALUES(5,'e')    
 INSERT INTO #ItemMasterFile VALUES(6,'f')    
 INSERT INTO #ItemMasterFile VALUES(7,'g')    
 INSERT INTO #ItemMasterFile VALUES(8,'h')    
 INSERT INTO #ItemMasterFile VALUES(9,'K')    
 INSERT INTO #ItemMasterFile VALUES(10,'L')    
 INSERT INTO #ItemMasterFile VALUES(11,'M')    
         
      
 INSERT INTO #Bigbaleprd VALUES(10006,3,1,400,'07-02-2022')    
 INSERT INTO #Bigbaleprd VALUES(10007,1,1,300,'07-02-2022')    
 INSERT INTO #Bigbaleprd VALUES(10008,3,1,200,'07-02-2022')    
 INSERT INTO #Bigbaleprd VALUES(10009,11,1,150,'07-02-2022')     
 INSERT INTO #Bigbaleprd VALUES(10010,8,1,100,'07-02-2022')     
 INSERT INTO #Bigbaleprd VALUES(10011,1,1,300,'08-02-2022')    
 INSERT INTO #Bigbaleprd VALUES(10012,3,1,200,'08-02-2022')    
 INSERT INTO #Bigbaleprd VALUES(10013,11,1,150,'08-02-2022')    
 INSERT INTO #Bigbaleprd VALUES(10014,10,1,250,'08-02-2022')    
 INSERT INTO #Bigbaleprd VALUES(10018,10,1,50,'08-02-2022')    
  
  INSERT INTO #Bigbaleprd VALUES(10018,9,1,800,'10-02-2022')    
 INSERT INTO #Bigbaleprd VALUES(10019,9,1,600,'10-02-2022')    
         
 INSERT INTO #DispatchM VALUES(1001,'08-02-2022')    
 INSERT INTO #DispatchM VALUES(1002,'09-02-2022')    
         
 INSERT INTO #DispatachD VALUES(1,1001,10011,1,1,300)    
 INSERT INTO #DispatachD VALUES(2,1001,10012,3,1,200)    
 INSERT INTO #DispatachD VALUES(3,1001,10013,11,1,150)    
 INSERT INTO #DispatachD VALUES(4,1001,10014,10,1,250)    
 INSERT INTO #DispatachD VALUES(5,1002,10015,8,1,900)    
 INSERT INTO #DispatachD VALUES(6,1002,10016,9,1,800)    
 INSERT INTO #DispatachD VALUES(7,1002,10017,9,1,600)    
;WITH CTE1 AS  
 (SELECT I.CodeItem  ,SUM(prdQTY)AS Opening_Qty,SUM(Bweight)AS Opening_Weight   
  FROM #ItemMasterFile I LEFT JOIN #Bigbaleprd B ON I.CodeItem=B.CodeItem   WHERE Entrydate<'10/02/2022'   GROUP BY I.CodeItem,Descriptionitem  
 ),CTE2 AS  
 (SELECT I.CodeItem ,SUM(prdQTY)AS In_Qty,SUM(Bweight)AS In_Weight   
  FROM #ItemMasterFile I LEFT JOIN #Bigbaleprd B ON I.CodeItem=B.CodeItem  
  WHERE Entrydate BETWEEN '10/02/2022'AND '10/02/2022'   GROUP BY I.CodeItem,Descriptionitem  
  
 ),CTE3 AS  
 (SELECT D.CodeItem,SUM(prdQTY)AS Out_Qty,SUM(Bweight)AS Out_Weight   
  FROM #DispatchM M LEFT JOIN #DispatachD D ON M.DID=D.DID  LEFT JOIN #ItemMasterFile I ON I.CodeItem= D.Codeitem  
   WHERE Entrydate BETWEEN '10/02/2022'AND '10/02/2022'   GROUP BY D.CodeItem,Descriptionitem)  
 SELECT UPPER(I.Descriptionitem) AS Item,ISNULL(Opening_Qty,0)AS Opening_Qty,ISNULL(Opening_Weight,0)AS Opening_Weight,  
        ISNULL(In_Qty,0)AS In_Qty,ISNULL(In_Weight,0)AS In_Weight,ISNULL(Out_Qty,0)AS Out_Qty,ISNULL(Out_Weight,0)AS Out_Weight,  
        ISNULL(Opening_QTY,0)+ISNULL(IN_QTY,0)-ISNULL(Out_QTY,0) AS Closing_Qty,  
        ISNULL(Opening_Weight,0) + ISNULL(IN_Weight,0) - ISNULL(Out_Weight,0) AS Closing_Weight    
 FROM #ItemMasterFile I LEFT JOIN CTE1 C1 ON I.CodeItem =C1.CodeItem  LEFT JOIN CTE2 C2 ON I.CodeItem =C2.CodeItem  LEFT JOIN CTE3 C3 ON I.CodeItem =C3.CodeItem

LiHong-MSFT 10,056 Reputation points

2022-02-14T06:40:58.707+00:00

Hi @Analyst_SQL

Item A Opening and closing Qty must be 1 not 2

Why Opening_QTY must be 1 ??
Opening_QTY means SUM(prdQTY) before 10/02/2022, am i right?
As youcan see in this picture,the CodeItem of Item A is '1'，and in table #Bigbaleprd , we can find two rows of data whose CodeItem is 1 and the date is before 10/02/2022.
From my point ,Opening_QTY should be 2, otherwise please explain why it must be 1.

Best regards,
LiHong
Analyst_SQL 3,576 Reputation points

2022-02-15T00:43:42.057+00:00

Hi @LiHong-MSFT

Opening will be calculated from last day Sum(Prdqty-Dispatch(Prdqty)),
means that
Closing_Qty (Opening_QTY+IN_QTY-Out_QTY) from Date between 08/02/2022 to 08/02/2022 , will be next day opening of 9/02/2022

Answer 3

Hi @Analyst_SQL
Please check this:

  GO  
  DECLARE @retrieve_date DATE ='10-02-2022';  
 ;WITH CTE1 AS  
 (SELECT I.CodeItem,ISNULL(SUM(B.prdQTY),0)-ISNULL(SUM(D.prdQTY),0)AS Opening_Qty, ISNULL(SUM(B.Bweight),0)-ISNULL(SUM(D.Bweight),0)AS Opening_Weight   
  FROM #ItemMasterFile I LEFT JOIN #Bigbaleprd B ON I.CodeItem=B.CodeItem  
                         LEFT JOIN #DispatachD D ON I.CodeItem=D.CodeItem AND B.BID=D.BID  
						 LEFT JOIN #DispatchM M ON  M.DID =D.DID AND B.Entrydate =M.Entrydate  
  WHERE B.Entrydate<@retrieve_date  
  GROUP BY I.CodeItem,Descriptionitem  
 ),CTE2 AS  
 (SELECT I.CodeItem ,SUM(prdQTY)AS In_Qty,SUM(Bweight)AS In_Weight   
  FROM #ItemMasterFile I LEFT JOIN #Bigbaleprd B ON I.CodeItem=B.CodeItem  
  WHERE Entrydate BETWEEN @retrieve_date AND @retrieve_date  
  GROUP BY I.CodeItem,Descriptionitem  
 ),CTE3 AS  
 (SELECT D.CodeItem,SUM(prdQTY)AS Out_Qty,SUM(Bweight)AS Out_Weight   
  FROM #DispatchM M LEFT JOIN #DispatachD D ON M.DID=D.DID  
                    LEFT JOIN #ItemMasterFile I ON I.CodeItem= D.Codeitem  
  WHERE Entrydate BETWEEN @retrieve_date AND @retrieve_date  
  GROUP BY D.CodeItem,Descriptionitem  
 )  
 SELECT UPPER(I.Descriptionitem) AS Item,ISNULL(Opening_Qty,0)AS Opening_Qty,ISNULL(Opening_Weight,0)AS Opening_Weight,  
        ISNULL(In_Qty,0)AS In_Qty,ISNULL(In_Weight,0)AS In_Weight,ISNULL(Out_Qty,0)AS Out_Qty,ISNULL(Out_Weight,0)AS Out_Weight,  
        ISNULL(Opening_QTY,0)+ISNULL(IN_QTY,0)-ISNULL(Out_QTY,0) AS Closing_Qty,  
        ISNULL(Opening_Weight,0) + ISNULL(IN_Weight,0) - ISNULL(Out_Weight,0) AS Closing_Weight    
 FROM #ItemMasterFile I LEFT JOIN CTE1 C1 ON I.CodeItem =C1.CodeItem  
                        LEFT JOIN CTE2 C2 ON I.CodeItem =C2.CodeItem  
                        LEFT JOIN CTE3 C3 ON I.CodeItem =C3.CodeItem

Best regards,
LiHong

Analyst_SQL 3,576 Reputation points

2022-02-16T06:43:07.973+00:00

HI @LiHong-MSFT

it can not be filter data between two date?
because i want to view data between two date ,that how much IN or OUT at that date duration

Answer 4

Hi @Analyst_SQL
Try this:

  DECLARE @retrieve_date_1 DATE ='09-02-2022';  
  DECLARE @retrieve_date_2 DATE ='10-02-2022';  
 ;WITH CTE1 AS  
 (SELECT I.CodeItem,ISNULL(SUM(B.prdQTY),0)-ISNULL(SUM(D.prdQTY),0)AS Opening_Qty,  
         ISNULL(SUM(B.Bweight),0)-ISNULL(SUM(D.Bweight),0)AS Opening_Weight   
  FROM #ItemMasterFile I LEFT JOIN #Bigbaleprd B ON I.CodeItem=B.CodeItem  
                         LEFT JOIN #DispatachD D ON I.CodeItem=D.CodeItem AND B.BID=D.BID  
						 LEFT JOIN #DispatchM M ON  M.DID =D.DID AND B.Entrydate =M.Entrydate  
  WHERE B.Entrydate<@retrieve_date_1  
  GROUP BY I.CodeItem,Descriptionitem  
 ),CTE2 AS  
 (SELECT I.CodeItem ,SUM(prdQTY)AS In_Qty,SUM(Bweight)AS In_Weight   
  FROM #ItemMasterFile I LEFT JOIN #Bigbaleprd B ON I.CodeItem=B.CodeItem  
  WHERE Entrydate BETWEEN @retrieve_date_1 AND @retrieve_date_2  
  GROUP BY I.CodeItem,Descriptionitem  
 ),CTE3 AS  
 (SELECT D.CodeItem,SUM(prdQTY)AS Out_Qty,SUM(Bweight)AS Out_Weight   
  FROM #DispatchM M LEFT JOIN #DispatachD D ON M.DID=D.DID  
                    LEFT JOIN #ItemMasterFile I ON I.CodeItem= D.Codeitem  
  WHERE Entrydate BETWEEN @retrieve_date_1 AND @retrieve_date_2  
  GROUP BY D.CodeItem,Descriptionitem  
 )  
 SELECT UPPER(I.Descriptionitem) AS Item,ISNULL(Opening_Qty,0)AS Opening_Qty,ISNULL(Opening_Weight,0)AS Opening_Weight,  
        ISNULL(In_Qty,0)AS In_Qty,ISNULL(In_Weight,0)AS In_Weight,ISNULL(Out_Qty,0)AS Out_Qty,ISNULL(Out_Weight,0)AS Out_Weight,  
        ISNULL(Opening_QTY,0)+ISNULL(IN_QTY,0)-ISNULL(Out_QTY,0) AS Closing_Qty,  
        ISNULL(Opening_Weight,0) + ISNULL(IN_Weight,0) - ISNULL(Out_Weight,0) AS Closing_Weight    
 FROM #ItemMasterFile I LEFT JOIN CTE1 C1 ON I.CodeItem =C1.CodeItem  
                        LEFT JOIN CTE2 C2 ON I.CodeItem =C2.CodeItem  
                        LEFT JOIN CTE3 C3 ON I.CodeItem =C3.CodeItem

Best regards,
LiHong

Share via

Stock query Require

4 answers

Your answer