question

ParvezAlam-6780 avatar image
0 Votes"
ParvezAlam-6780 asked MelissaMa-msft answered

SQL Running Total Query

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]


[1]: /answers/storage/attachments/132956-po-table.jpg

sql-server-transact-sql
po-table.jpg (53.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ParvezAlam-6780

Welcome to Microsoft Q&A!

The below is my dynamic selection for DC
K1 A1 C1 3500

Could you please provide more details about above? Where does this row of data come from? Another table?

DC Qty PO Bal Qty

Could you please also provide the rules of above two columns in your need output? Where is 532 from?

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ParvezAlam-6780

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:
133464-output.png

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.



output.png (3.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ParvezAlam-6780 avatar image
0 Votes"
ParvezAlam-6780 answered ParvezAlam-6780 edited

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'

132947-image.png





image.png (6.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ParvezAlam-6780

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:
132917-output.png
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.




output.png (3.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ParvezAlam-6780

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:
133032-output.png

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.


output.png (3.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ParvezAlam-6780 avatar image
0 Votes"
ParvezAlam-6780 answered

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

![132979-image.png][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




image.png (14.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ParvezAlam-6780 avatar image
0 Votes"
ParvezAlam-6780 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.