Calculate Running Total Summary based on Different column

I need output like below

StockQty = 10

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

About admin

administrator