Item ledger query require?

Question

I want item ledger query ,For Your understanding ,that i am inserting item into #Probale table then it will be IN ,then i am making Bundle in #PackM table,then I am dispatch Bundle in #DispatchM ,
Below is Data and expected output,

I want to See ledger of item A,

CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50))    
     
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Entrydate DATETIME)    
     
CREATE TABLE #PackM(PID INT, Entrydate DATETIME)    
CREATE TABLE #PackD(DID INT,PID int,BID int,Codeitem int,prdQTY int)    
     
CREATE TABLE #DispatchM(DID INT, Entrydate DATETIME)    
Create Table #DispatachD(ID int ,DID int,PID 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 #Probale VALUES(10011,1,1,'01-06-2019')    
INSERT INTO #Probale VALUES(10012,3,1,'02-06-2019')    
INSERT INTO #Probale VALUES(10013,11,1,'03-06-2019')    
INSERT INTO #Probale VALUES(10014,10,1,'08-06-2019')    
INSERT INTO #Probale VALUES(10015,8,1,'03-06-2019')    
INSERT INTO #Probale VALUES(10016,9,1,'08-06-2019')    
INSERT INTO #Probale VALUES(10017,9,1,'08-06-2019')    
     
INSERT INTO #PackM VALUES(1001,'02-06-2019')    
INSERT INTO #PackM VALUES(1002,'03-06-2019')    
     
INSERT INTO #PackD VALUES(1,1001,10011,1,1)    
INSERT INTO #PackD VALUES(2,1001,10012,3,1)    
INSERT INTO #PackD VALUES(3,1001,10013,11,1)    
INSERT INTO #PackD VALUES(4,1001,10014,10,1)    
INSERT INTO #PackD VALUES(5,1002,10015,8,1)    
INSERT INTO #PackD VALUES(6,1002,10016,9,1)    
INSERT INTO #PackD VALUES(7,1002,10017,9,1)    
     
INSERT INTO #DispatchM VALUES(101,'04-06-2019')    
INSERT INTO #DispatchM VALUES(102,'05-06-2019')    
     
INSERT INTO #DispatachD VALUES(1,101,1001)    
INSERT INTO #DispatachD VALUES(1,102,1002)   

Answer 1

Hi @Analyst_SQL ,

Please refer to:

     ;WITH cte  
     as(SELECT I.CodeItem,I.Descriptionitem,P.Entrydate,P.BID,P.prdQTY FROM #ItemMasterFile I  
     JOIN #Probale P ON I.CodeItem=P.CodeItem  
     WHERE I.Descriptionitem='A' )  
     ,cte2 as  
     (SELECT c.*,PD.PID,PD.BID BID2 FROM cte c  
     LEFT JOIN #PackD PD ON c.BID=PD.BID)  
     ,cte3 as  
     (SELECT c2.*,DD.DID FROM cte2 c2  
     LEFT JOIN #DispatachD DD ON c2.PID=DD.PID)  
     ,cte4 as  
     (SELECT Descriptionitem Item,BID Code,Entrydate,NULL Remarks,prdQTY [IN],NULL Pack,NULL [Out] FROM cte3  
     UNION ALL  
     SELECT NULL,NULL,Entrydate,PID,NULL,(SELECT  DISTINCT prdQTY  FROM cte3),NULL FROM #PackM WHERE PID IN(SELECT PID FROM cte3)  
     UNION ALL  
     SELECT NULL,NULL,Entrydate,DID,NULL,NULL,(SELECT DISTINCT prdQTY  FROM cte3) FROM #DispatchM WHERE DID IN(SELECT DID FROM cte3))  
          
     SELECT * FROM cte4  
     UNION ALL  
     SELECT 'Total',NULL,NULL,NULL,SUM([IN]),SUM(Pack),SUM([Out]) FROM cte4  

When I first read your post, I spent some time looking for [IN], Pack, [Out] calculation rules,but did not find.I suggest you try to explain the calculation rules of all your data when you post a new question, so that you can solve the problem quickly.

Regards
Echo

Answer 2

Hi @Analyst_SQL ,

What are the calculation rules for your IN, Pack, and Out columns? I chose the value of CodeItem as their value. If your rule is not like this, you can make some modifications to the following code:

;WITH cte  
as(SELECT I.CodeItem,I.Descriptionitem,P.Entrydate,P.BID FROM #ItemMasterFile I  
JOIN #Probale P ON I.CodeItem=P.CodeItem  
WHERE I.Descriptionitem='A' )  
,cte2 as  
(SELECT c.*,PD.PID,PD.BID BID2 FROM cte c  
JOIN #PackD PD ON c.BID=PD.BID)  
,cte3 as  
(SELECT c2.*,DD.DID FROM cte2 c2  
JOIN #DispatachD DD ON c2.PID=DD.PID)  
,cte4 as  
(SELECT Descriptionitem Item,BID Code,Entrydate,NULL Remarks,CodeItem [IN],NULL Pack,NULL [Out] FROM cte3  
UNION ALL  
SELECT NULL,NULL,Entrydate,PID,NULL,(SELECT CodeItem FROM cte3),NULL FROM #PackM WHERE PID IN(SELECT PID FROM cte3)  
UNION ALL  
SELECT NULL,NULL,Entrydate,DID,NULL,NULL,(SELECT CodeItem FROM cte3) FROM #DispatchM WHERE DID IN(SELECT DID FROM cte3))  
  
SELECT * FROM cte4  
UNION ALL  
SELECT 'Total',NULL,NULL,NULL,SUM([IN]),SUM(Pack),SUM([Out]) FROM cte4  

Out:

Total is placed in the first column because the data type of the Remarks column is not a string, and they cannot use the union all keyword.

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.
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.

Answer 3

   INSERT INTO #Probale VALUES(10020,1,1,'01-07-2019')   


;WITH cte  
as(SELECT I.CodeItem,I.Descriptionitem,P.Entrydate,P.BID FROM #ItemMasterFile I  
JOIN #Probale P ON I.CodeItem=P.CodeItem  
WHERE I.Descriptionitem='A' )  
,cte2 as  
(SELECT c.*,PD.PID,PD.BID BID2 FROM cte c  
LEFT JOIN #PackD PD ON c.BID=PD.BID)  
,cte3 as  
(SELECT c2.*,DD.DID FROM cte2 c2  
LEFT JOIN #DispatachD DD ON c2.PID=DD.PID)  
,cte4 as  
(SELECT Descriptionitem Item,BID Code,Entrydate,NULL Remarks,CodeItem [IN],NULL Pack,NULL [Out] FROM cte3  
UNION ALL  
SELECT NULL,NULL,Entrydate,PID,NULL,(SELECT  DISTINCT CodeItem FROM cte3),NULL FROM #PackM WHERE PID IN(SELECT PID FROM cte3)  
UNION ALL  
SELECT NULL,NULL,Entrydate,DID,NULL,NULL,(SELECT DISTINCT CodeItem FROM cte3) FROM #DispatchM WHERE DID IN(SELECT DID FROM cte3))  
  
SELECT * FROM cte4  
UNION ALL  
SELECT 'Total',NULL,NULL,NULL,SUM([IN]),SUM(Pack),SUM([Out]) FROM cte4  
  

Echo