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

Thanks to all

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  

DC Table  
  
CREATE TABLE [dbo].[DCQty](  
	[ID] [int] IDENTITY(1,1) NOT NULL,  
	[ItemName] [nvarchar](10) NULL,  
	[Color] [nvarchar](10) NULL,  
	[DCQty] [float] NULL,  
 CONSTRAINT [PK_TestDCQty] 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  

This is how i join this table  
  
Select A.PONO,A.ItemName,A.Color,A.POQty,CASE WHEN B.DCQty >= 0 THEN A.POQty WHEN B.DCQty < - A.POQty THEN 0 ELSE B.DCQty + A.POQty END as DCQty  
,CASE WHEN B.DCQty >= 0 THEN 0 WHEN B.DCQty < - A.POQty THEN A.POQty ELSE ABS(B.DCQty) END as POBalQty 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'  
  

Answer 3

Hi @Parvez Alam ，

I worked with below query before DCQty table provided. You could refer it and check whether it is helpful.

declare @pono nvarchar(10)='K1'  
declare @itemname nvarchar(10)='A1'  
declare @color nvarchar(10)='C1'  
declare @dcqty float =3500  
  
;with cte as (  
select *,isnull(lag(POQty) over (partition by ItemName,Color order by id,pono),POQty) POQty1   
from [PO]  
where ItemName=@itemname and Color=@color)  
,cte1 as (  
select *,case when PONO=@pono then POQty1 else @dcqty-POQty1 end DCQty  
from cte)  
select PONO,ItemName,color,POqty,dcqty, POQty-DCQty POBalQty  
from cte1  
union   
select null,null,null,null,@dcqty,sum(POQty-DCQty) from cte1  

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 4

Hi @Parvez Alam ，

Please also refer below one which includes [DCQty] table.

;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 *,POQty-DCQty POBalQty from cte2  
union   
select null,null,null,null,sum(POQty),sum(POQty-DCQty) from cte2) a  
order by isnull(PONO,'ZZZ')  

Output:

If above is not working, please provide more sample data and expected 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 5

I just changed PO Qty for K1 as 3000 and K2 as 4000. and DC Qty as 2700.
The result show incorrect ...

]1

The above should be the output ...
But it shows different DC Qty

PONO    ItemName    Color   POQty   DCQty   POBalQty  
K1  A1  C1  3000    3000    0  
K2  A1  C1  4000    -300    4300