SQL Query for Balance Running Total

Parvez Alam 21 Reputation points
2021-09-29T05:48:47.777+00:00

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  
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Parvez Alam 21 Reputation points
    2021-09-29T06:17:04.603+00:00
    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 ....


  2. Parvez Alam 21 Reputation points
    2021-09-29T07:09:50.827+00:00

    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
    136179-image.png

    0 comments No comments

  3. EchoLiu-MSFT 14,581 Reputation points
    2021-09-30T08:12:22.963+00:00

    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:
    136550-image.png

    Echo

    0 comments No comments

  4. Parvez Alam 21 Reputation points
    2021-09-30T09:41:13.163+00:00

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

    136672-image.png

    0 comments No comments

  5. Parvez Alam 21 Reputation points
    2021-10-13T04:02:08.43+00:00

    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.

    0 comments No comments