After going through the query, if found i have missed a case and i have fixed it.
Following is the issue:
fixed issue: If 1st DemandQty is greater then the available quantity the Transferred column was showing DemandQty not the AvailableQty.
Here is the latest code, i have also added the CASE 3.
declare @Transaction table
(
ID int
, Store char(3)
, SKU int
, AvailableQty int
, ReceivingStore char(3)
, QtyDemand int
)
insert into @Transaction
--- Original Case
select 1, 'ST1', 100, 20, 'ST2', 14union all
select 2, 'ST1', 100, 20, 'ST3', 25union all
select 3, 'ST1', 100, 20, 'ST4', 25
;
--- Sample Case 1
insert into @Transaction
select 4, 'ST1', 101, 20, 'ST2', 2union all
select 5, 'ST1', 101, 20, 'ST3', 5union all
select 6, 'ST1', 101, 20, 'ST4', 25
;
--- Sample Case 2
insert into @Transaction
select 4, 'ST1', 102, 20, 'ST2', 20union all
select 5, 'ST1', 102, 20, 'ST3', 5union all
select 6, 'ST1', 102, 20, 'ST4', 25
;
--- Sample Case 3----- Added the case if 1st value is greater then the available quantity.
insert into @Transaction
select 4, 'ST1', 103, 20, 'ST2', 25union all
select 5, 'ST1', 103, 20, 'ST3', 5union all
select 6, 'ST1', 103, 20, 'ST4', 5
;
WITH CTE
AS
(
select a.*, case when a.rn = 1 then AvailableQty - QtyDemand else null end as OpenBal
from
(
select *
, ROW_NUMBER() OVER (Partition by SKU ORDER BY ID) as rn
from @Transaction
) A
)
Select ID,Store, Sku, AvailableQty, ReceivingStore, QtyDemand, case when transfered = 0 then PrevBalc else transfered end as transfered
from
(
select *
---- Updated fixed issue: 1st DemandQty is greater then the available quantity.
---, case when rn = 1 then QtyDemand else case when closeing < 0 then 0 else QtyDemand end end as transfered
, case
when rn = 1 then
Case
when QtyDemand > AvailableQty then AvailableQty
ELSE QtyDemand
END
else
case
when closeing < 0 then 0
else
QtyDemand
end
end as transfered
---- Ends here
, case when LAG(closeing) OVER (Partition by sku ORDER BY ID) > 0 THEN LAG(closeing) OVER (Partition by sku ORDER BY ID) else 0 end AS PrevBalc
from
(
select * , SUM(coalesce(OpenBal, -1 * QtyDemand)) OVER (Partition by sku ORDER BY ID ROWS UNBOUNDED PRECEDING) as closeing
from cte c
) A
) A