SQL Running Subtraction Total

Last Reply 3 days ago By dharmendr

Posted 28 days 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

 

You are viewing reply posted by: dharmendr 3 days ago.
Posted 3 days ago
ashraft1 says:
 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])

Issue is in your condition in the while statement.

2nd condition not working.