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
CodePudding user response：
You can do it using a recursive cte
with
cte as
(
select *, rn = row_number() over (partition by ItemId order by CreateDate)
from @tbl
),
rcte as
(
-- anchor member
select rn, ItemId, StockQty, BalanceQty,
[Allocated Stock Qty] = case when StockQty > BalanceQty
then BalanceQty
else StockQty
end,
[Shortage Stock] = case when StockQty > BalanceQty
then 0
else BalanceQty - StockQty
end,
[Running Stock] = StockQty - BalanceQty
from cte
where rn = 1
union all
-- recursive member
select c.rn, c.ItemId, r.StockQty, c.BalanceQty,
[Allocated Stock Qty] = case when r.[Running Stock] > c.BalanceQty
then c.BalanceQty
else r.[Running Stock]
end,
[Shortage Stock] = case when r.[Running Stock] > c.BalanceQty
then 0
else c.BalanceQty - r.[Running Stock]
end,
[Running Stock] = case when r.[Running Stock] - c.BalanceQty > 0
then r.[Running Stock] - c.BalanceQty
else 0
end
from cte c
inner join rcte r on c.ItemId = r.ItemId
and c.rn = r.rn 1
)
select *
from rcte
order by rn
CodePudding user response：
declare @tbl as table
(
ItemId int,
BalanceQty int,
CreateDate datetime,
StockQty int
)
insert into @tbl values
(1,3,'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
;with
cte as
(
select *, rn = row_number() over (partition by ItemId order by CreateDate)
from @tbl
),
rcte as
(
-- anchor member
select rn, ItemId, StockQty, BalanceQty,
[Allocated Stock Qty] = BalanceQty,
[Shortage Stock] = case when StockQty > BalanceQty
then 0
else abs(BalanceQty - StockQty)
end,
[Running Stock] = case when StockQty - BalanceQty < 0
then 0
else abs(BalanceQty - StockQty)
end
from cte
where rn = 1
union all
-- recursive member
select c.rn, c.ItemId, r.StockQty, c.BalanceQty,
[Allocated Stock Qty] = r.[Running Stock],
[Shortage Stock] = case when r.[Running Stock] > c.BalanceQty
then 0
else c.BalanceQty - r.[Running Stock]
end,
[Running Stock] = case when r.[Running Stock] - c.BalanceQty > 0
then r.[Running Stock] - c.BalanceQty
else 0
end
from cte c
inner join rcte r on c.ItemId = r.ItemId
and c.rn = r.rn 1
)
select *
from rcte
order by rn