Calculate Running Total Summary based on Different column

Question

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

Your question is not very clear. You stated StockQty = 10 in the beginning of the question and in @tbl there is also a StockQty with various value. Are those the same ? Also you seems to be ignoring the value of StockQty in @tbl — Squirrel
– Squirrel, Commented Dec 29, 2021 at 4:14
use StockQty for calculation of running stock that's why i set in to 1st row — Zoyeb Shaikh
– Zoyeb Shaikh, Commented Dec 29, 2021 at 4:18
i need to hold it somewhere so i set in table first row where order by create date — Zoyeb Shaikh
– Zoyeb Shaikh, Commented Dec 29, 2021 at 4:21

Squirrel · Accepted Answer · 2021-12-29 05:06:39Z

1

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

answered Dec 29, 2021 at 5:06

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zoyeb Shaikh Over a year ago

i have modified your asnwer as per my need, thank you so much @Squirrel for your support

Zoyeb Shaikh Over a year ago

I am not getting an idea how to perform it on row by row basis

Zoyeb Shaikh · Accepted Answer · 2021-12-29 08:20:25Z

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

You don't need the 2 update statement at all. The first update statement is not doing anything except taking the StockQty from first row (based on order by CreateDate) and update back itself only. The second update statement set StockQty to 0. In my query, It only uses the StockQty from the first row and ignore the rest

Collectives™ on Stack Overflow

Calculate Running Total Summary based on Different column

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related