SQL Running Total Query

Question

This is my PO Table

CREATE TABLE [dbo].[PO](  
	[ID] [int] IDENTITY(1,1) NOT NULL,  
	[PONO] [nvarchar](10) NULL,  
	[ItemName] [nvarchar](10) NULL,  
	[Color] [nvarchar](10) NULL,  
	[POQty] [float] NULL,  
 CONSTRAINT [PK_TestPO] 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  
  

![PO Table					  
PONO	Item Name	Color	POQty		  
K1	A1	C1	2968		  
K2	A1	C1	2969		  
K3	A2	C2	2180		  
K4	A3	C3	2968		  
K5	A4	C4	3224		  
					  
The below is my dynamic selection for DC					  
					  
K1	A1	C1	3500		  
					  
					  
Need Output As					  
PONO	Item Name	Color	POQty	DC Qty	PO Bal Qty  
K1	A1	C1	2968	2968	0  
K2	A1	C1	2969	532	2437  
				3500	2437][1]  
  

Answer 1

Hi @Parvez Alam ，

Please refer below updated one and check whether it is working.

;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,* from [po]) A   
LEFT JOIN [DCQty] 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 *,  
 POQty-DCQty POBalQty  
from cte1  
union  
select null,null,null,null,sum(DCQty),sum(POQty-DCQty) from cte1) a  
order by isnull(PONO,'ZZZ')  

Output:

Best regards,
Melissa

---

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 2

I just tried this
;with cte as (
Select A.PONO,A.ItemName,A.Color,A.POQty,b.DCQty DCQty1
FROM [PO] A LEFT JOIN [DCQty] 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(POQty) over (partition by ItemName,Color order by pono),POQty) POQty1 ,DCQty1
from cte)
,cte2 as (
select PONO,ItemName,Color,POQty,
case when POQty=POQty1 then POQty else DCQty1-POQty1 end DCQty
from cte1)
select * from (
select *,
case when POQty >= DCQty then POQty-DCQty else DCQty-POQty end POBalQty
from cte2
union
select null,null,null,null,sum(POQty),sum(POQty-DCQty) from cte2) a
order by isnull(PONO,'ZZZ')

This work fine when DC Qty is greater than PO Qty.
If DC QTy is less then PO Qty then it takes PO Qty as DC Qty.
Say for example i have changed DC Qty as 2900.
Then see the below out put

PONO ItemName Color POQty DCQty POBalQty
K1 A1 C1 3000 3000 0
K2 A1 C1 4000 -100 4100

Though DC Qty is 2900 it takes DC Qty as 3000.
What is wrong in the above query?