SQL Running Subtraction Total

Last Reply 11 days ago By dharmendr

Posted one month ago

 

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

 

;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')

The above query is working fine DC Qty is greater than PO Qty

If DC Qty is less than PO Qty then it shows a different result.

 

 

PO Table			
PONO	Item Name	Color	POQty
K1	A1	C1	3000
K2	A1	C1	4000

 

DC Table
Item Name	Color	POQty
A1	C1	2700

 

Need Output As					
PONO	Item Name	Color	POQty	DC Qty	PO Bal Qty
K1	A1	C1	2968	2700	268
K2	A1	C1	2969	0	2969
				2700	2437

 

ashraft1 has viewed this question 9 days ago.
Results 1 - 5 of 23 12345
Posted one month ago

I will get back to you soon.


Posted one month ago

Refer below query.

;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')

 


Posted one month ago

 

I tried the query but this work only when DC Qty is greater than PO Qty.
If the DC Qty is less than PO Qty then it shows wrong output.

Say for example i have DC Qty as 2900, now the out put shows like below

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

Here DC Qty is 2900 but the query takes DC Qty as 3000.


Posted one month ago

Hi ashraft1,

Share sample record.


Posted one month ago

 

This is table structure

 

PO Table					
PONO	Item Name	Color	POQty		
K1	A1	C1	3000		
K2	A1	C1	4000		
					
DC Table					
PONO	Item Name	Color	POQty		
K1	A1	C1	2700		
					
Need Output As					
PONO	Item Name	Color	POQty	DC Qty	PO Bal Qty
K1	A1	C1	2968	2700	268
K2	A1	C1	2969	0	2969
				2700	2437