I need output like below
StockQty = 10
Balance Qty | [Allocate Stock Qty] | Shortage [Stock] | Running Stock |
---|---|---|---|
5.000 | 5.000 | 0.000 | 5.000 |
10.000 | 5.000 | 5.000 | 0.000 |
5.000 | 0.000 | 5.000 | 0.000 |
10.000 | 0.000 | 10.000 | 0.000 |
15.000 | 0.000 | 15.000 | 0.000 |
I have tried so far but could not make it
declare @tbl as table
(
ItemId int,
BalanceQty int,
CreateDate datetime,
StockQty int
)
insert into @tbl values
(1,5,'2021-12-16 19:28:32.200',10),
(1,10,'2021-12-18 19:28:34.200',30),
(1,5,'2021-12-19 19:28:35.200',30),
(1,10,'2021-12-21 19:28:35.200',30),
(1,15,'2021-12-22 19:28:35.200',30)
update x
set x.StockQty = tx.StockQty
from @tbl x
join
(
select *
from
(
select *,
ROW_NUMBER()over(partition by itemid order by CreateDate) as RowNo
from @tbl
) as t
where t.RowNo = 1
) as tx on tx.CreateDate = x.CreateDate
update x
set x.StockQty = 0
from @tbl x
join
(
select *
from
(
select *,
ROW_NUMBER()over(partition by itemid order by CreateDate) as RowNo
from @tbl
) as t
where t.RowNo != 1
) as tx on tx.CreateDate = x.CreateDate
select *,
case when SUM(StockQty - BalanceQty)
over(partition by ItemId
order by CreateDate Rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) < 0
then 0
else SUM(StockQty - BalanceQty)
over(partition by ItemId order by CreateDate Rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
end as [Running Stock]
from @tbl
--ORDER BY CreateDate
I want to calcualte running stock summary based on given data For the first Row StockQty = 10 , [Allocate Stock Qty] = StockQty – Balance Qty [will give you 5] [Running Stock] = left stock Qty [5 utilize and 5 left] Shortage [Stock] will be zero because no Shortage yet
For second Row StockQty = 5 because we have used in 1st row [Allocate Stock Qty] = what is left from running stock i.e 5 [Running Stock] = nothing left so 0 Shortage [Stock] will be 5 because we allocate 5 and we dont have stock and so on