SQL Query for Balance Running Total

Question

This is my previous thread
sql-running-total-query.html

Continue to the above query ...
PO Table
CREATE TABLE [dbo].PO ON [PRIMARY]
GO
PO Insert

GO  
SET IDENTITY_INSERT [dbo].[PO] ON   
  
INSERT [dbo].[PO] ([ID], [PONO], [ItemName], [Color], [POQty]) VALUES (1, N'K1', N'A1', N'C1', 5240)  
INSERT [dbo].[PO] ([ID], [PONO], [ItemName], [Color], [POQty]) VALUES (2, N'K2', N'A1', N'C1', 393)  
INSERT [dbo].[PO] ([ID], [PONO], [ItemName], [Color], [POQty]) VALUES (3, N'K3', N'A1', N'C1', 4847)  
INSERT [dbo].[PO] ([ID], [PONO], [ItemName], [Color], [POQty]) VALUES (4, N'K4', N'A3', N'C3', 2968)  
INSERT [dbo].[PO] ([ID], [PONO], [ItemName], [Color], [POQty]) VALUES (5, N'K5', N'A4', N'C4', 3224)  
SET IDENTITY_INSERT [dbo].[PO] OFF  

DC Table  
  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
CREATE TABLE [dbo].[DC](  
	[ID] [int] IDENTITY(1,1) NOT NULL,  
	[ItemName] [nvarchar](10) NULL,  
	[Color] [nvarchar](10) NULL,  
	[DCQty] [float] NULL,  
 CONSTRAINT [PK_DCQty] PRIMARY KEY CLUSTERED   
(  
	[ID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
GO  
DC Insert  
  
GO  
SET IDENTITY_INSERT [dbo].[DC] ON   
  
INSERT [dbo].[DC] ([ID], [ItemName], [Color], [DCQty]) VALUES (1, N'A1', N'C1', 9500)  
SET IDENTITY_INSERT [dbo].[DC] OFF  

I got solution from forum
;with cte as
( Select A.PONO,A.ItemName,A.Color,A.POQty,CASE WHEN RN=1 THEN CASE WHEN POQty>B.DCQty THEN B.DCQty ELSE POQty END ELSE 0 end DCQty,CASE WHEN RN=1 THEN b.DCQty ELSE 0 END DCQty1
FROM
(
Select ROW_NUMBER() OVER ( PARTITION BY ItemName,Color ORDER BY PONO ) RN,PONO,ItemName,Color,POQty FROM [PO] ) A
LEFT JOIN [DC] B On A.ItemName=B.ItemName AND A.Color=B.Color
Where A.ItemName='A1' and A.Color='C1'
)
,cte1 as ( Select PONO,ItemName,Color,POQty,ISNULL(lag(DCQty1-DCQty) OVER ( PARTITION BY ItemName,Color Order By PONO ),DCQty ) DCQty from cte )

Select * FROM ( Select PONO,ItemName,Color,POQty,DCQty,POQty-DCQty POBalQty FROM cte1 Where DCQty>0 ) a  

This works fine for the first PO DC Qty. But for 2nd PO K2 has 393 Qty. But in DC it has taken balance all remaining DC Qty.

This is the output i am getting which has wrong POQty is less than DC Qty in 2nd row if so then it should take 3rd PO

PONO	ItemName	Color	POQty	DCQty	POBalQty  
K1	A1	C1	5240	5240	0  
K2	A1	C1	393	4260	-3867  

Answer 1

This is the query
;with cte as 
( Select A.PONO,A.ItemName,A.Color,A.POQty,CASE WHEN RN=1 THEN CASE WHEN POQty>B.DCQty THEN B.DCQty ELSE POQty END  ELSE 0 end DCQty,CASE WHEN RN=1 THEN b.DCQty ELSE 0 END DCQty1
FROM 
( 
Select ROW_NUMBER() OVER ( PARTITION BY ItemName,Color ORDER BY PONO ) RN,PONO,ItemName,Color,POQty FROM [PO] ) A 
LEFT JOIN [DC] B On A.ItemName=B.ItemName AND A.Color=B.Color
Where A.ItemName='A1' and A.Color='C1' 
)
,cte1 as ( Select PONO,ItemName,Color,POQty,ISNULL(lag(DCQty1-DCQty) OVER ( PARTITION BY ItemName,Color Order By PONO ),DCQty ) DCQty from cte )

Select * FROM ( Select PONO,ItemName,Color,POQty,DCQty,POQty-DCQty POBalQty FROM cte1 Where DCQty>0 ) a

PONO ItemName Color POQty DCQty POBalQty
K1 A1 C1 5240 5240 0
K2 A1 C1 393 4260 -3867

The above is the output.
In K2 PONO PO Qty is less than DCQty then it should check the next PO right?
What is wrong here ....

Answer 2

I need to segregate POQty based on DCQty. Here PO and DC Table have a common field name as ItemName and Color based on this column I am matching PO Table with DC Table.

User will pass bulk DCQty. Based on ItemName and Color query should check the PONO and POQty and match that with DCQty. The condition for each PO NO is DC Qty should be less than PO Qty if DC Qty is greater then PO Qty then query should check for next PO NO and PO Qty

From the query this is the output.
PONO ItemName Color POQty DCQty POBalQty
K1 A1 C1 5240 5240 0
K2 A1 C1 393 4260 -3867
K3 A1 C1 4847 0 4847

Here K2 has POQty as 393 but the system has taken DCQty as 4260.
DCQty should be less than or equal to POQty.
If DCQty has more than POQty then system should check for next PO NO and PO Qty.
K3 has 4847 which mean query should have taken 3867 as DC Qty.

This should be the output

Answer 3

Please check:

     CREATE TABLE #test(ID INT,DCQty INT);  
      
     DECLARE @i INT  
     SET @i=1  
     WHILE @i<=(SELECT MAX(ID) FROM [PO]) AND   
     (SELECT SUM(POQty) FROM [PO] WHERE ID<=  
     CASE WHEN @i=1 THEN 1 ELSE @i-1 END)<=(SELECT DCQty FROM [DC])  
     BEGIN   
      
      INSERT INTO #test VALUES(@i,  
      CASE WHEN @i=1 THEN (SELECT POQty FROM [PO] WHERE ID=@i)  
      WHEN @i>1 AND  
      (SELECT POQty FROM [PO] WHERE ID=@i)  
     <(SELECT DCQty FROM [DC])-(SELECT SUM(POQty)   
      FROM [PO] WHERE ID<=@i-1)  
      THEN (SELECT POQty FROM [PO] WHERE ID=@i)  
      WHEN @i>1 AND (SELECT POQty FROM [PO] WHERE ID=@i)  
     >(SELECT DCQty FROM [DC])-(SELECT SUM(POQty) FROM [PO] WHERE ID<=@i-1)  
     THEN (SELECT DCQty FROM [DC])-(SELECT SUM(POQty) FROM [PO] WHERE ID<=@i-1)  
     END)  
       
     SET @i=@i+1  
    END;  
      
    SELECT p.*,t.DCQty,POBa=p.POQty-t.DCQty FROM  [PO] p  
    JOIN #test t  
    ON p.ID=t.ID  

Output:

Echo

Answer 4

Thanks for the help I just tried the above query ....

CREATE TABLE #test(ID INT,DCQty INT);  
          
      DECLARE @i INT  
      SET @i=1  
      WHILE @i<=(SELECT MAX(ID) FROM [PO]) AND   
      (SELECT SUM(POQty) FROM [PO] WHERE ID<=  
      CASE WHEN @i=1 THEN 1 ELSE @i-1 END)<=(SELECT DCQty FROM [DC])  
      BEGIN   
          
       INSERT INTO #test VALUES(@i,  
       CASE WHEN @i=1 THEN (SELECT POQty FROM [PO] WHERE ID=@i)  
       WHEN @i>1 AND  
       (SELECT POQty FROM [PO] WHERE ID=@i)  
      <(SELECT DCQty FROM [DC])-(SELECT SUM(POQty)   
       FROM [PO] WHERE ID<=@i-1)  
       THEN (SELECT POQty FROM [PO] WHERE ID=@i)  
       WHEN @i>1 AND (SELECT POQty FROM [PO] WHERE ID=@i)  
      >(SELECT DCQty FROM [DC])-(SELECT SUM(POQty) FROM [PO] WHERE ID<=@i-1)  
      THEN (SELECT DCQty FROM [DC])-(SELECT SUM(POQty) FROM [PO] WHERE ID<=@i-1)  
      END)  
           
      SET @i=@i+1  
     END;  
          
     SELECT p.*,t.DCQty,POBa=p.POQty-t.DCQty FROM  [PO] p  
     JOIN #test t  
     ON p.ID=t.ID  

Majorly faced 2 issues in the above query
1st
If the DC Qty is less than PO Qty then it does not return any resultset I mean the resultset is empty.
Eg: K1 PO Qty is 5240 and DC Qty is 5000 then the result is empty.
2nd
If the DC Qty is 5633 which is actually the total qty of PONO K1,K2 which is 5633 of both K1,K2 PO Qty.
Then K2 DC Qty return as NULL.
See here ...

Answer 5

Any update on the above query .... ??

I have updated the query ...

 CREATE TABLE #test(ID INT,DCQty INT);

       DECLARE @i INT
       SET @i=1

       WHILE @i<=(SELECT MAX(ID) FROM [PO]) AND ( SELECT SUM(POQty) FROM [PO] WHERE ID<= CASE WHEN @i=1 THEN 1 ELSE @i-1 END)<=(SELECT ISNULL(DCQty,0) as DCQty FROM [DC])

       BEGIN 

        INSERT INTO #test VALUES(@i,
        CASE WHEN @i=1 THEN (SELECT POQty FROM [PO] WHERE ID=@i)
        WHEN @i>1 AND
        (SELECT POQty FROM [PO] WHERE ID=@i)

       < ( SELECT ISNULL(DCQty,0) as DCQty FROM [DC])-(SELECT SUM(POQty) FROM [PO] WHERE ID<=@i-1) THEN (SELECT POQty FROM [PO] WHERE ID=@i )
        WHEN @i>1 AND ( SELECT POQty FROM [PO] WHERE ID=@i )

       > ( SELECT DCQty FROM [DC])-(SELECT SUM(POQty) FROM [PO] WHERE ID<=@i-1) THEN (SELECT ISNULL(DCQty,0) as DCQty FROM [DC])-(SELECT SUM(POQty) FROM [PO] WHERE ID<=@i-1)


       END 

       )

       SET @i=@i+1
      END;

      SELECT p.*,ISNULL(t.DCQty,p.POQty) as DCQty,POBal=p.POQty-ISNULL(t.DCQty,p.POQty) FROM  [PO] p
      JOIN #test t ON p.ID=t.ID
      Where ISNULL(t.DCQty,p.POQty)>0

      Drop Table #test

The above has just one issue

If the DC Qty is less than PO Qty then it does not return any resultset I mean the resultset is empty.
Eg: K1 PO Qty is 5240 and DC Qty is 4000 then the result is empty.