Home > Mobile >  Calculate Running Total Summary based on Different column
Calculate Running Total Summary based on Different column

Time:12-29

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
  • Related