SQL SELECT with cumulative calculation across selected rows

Question

Hello everyone and many thanks in advance for your help.

I got stuck on a calculated column on a query. I want to calculate how stock of an item is decreasing on the needed units for each order so I have the remaining stock info on the column CALCULATEDSTOCK.

For the first occurence of every ARTICLE & COLOR & SIZE the CALCULATEDSTOCK is the "initial" STOCK, and for the second and next occurences of the same ARTICLE & COLOR & SIZE the CALCULATEDSTOCK is decreased by the previous units NEEDED, so I get the available stock for that line.

Note that STOCK is always the same as is a direct query to the database.

This is the results I would like to get:

ORDER   ARTICLE  COLOR    SIZE   STOCK   NEEDED   CALCULATEDSTOCK
-----------------------------------------------------------------
43002   1000     GREY     L      13      4        13
43002   1000     GREY     XL     20      5        20
43006   1000     GREY     XL     20      4        15 
43012   1000     GREY     XL     20      6        11
43021   1000     GREY     XL     20      2        5
43021   1000     PURPLE   M      7       2        7
43023   1000     PURPLE   L      6       3        6

Find below what I have tried but I can't apply the LAG command to the previous CALCULATEDSTOCK column, so I can not calculate for more than two rows...

SELECT ORDER, ARTICLE, COLOR, SIZE, STOCK, NEEDED,
CAST( CASE WHEN ARTICLE = LAG(ARTICLE) OVER (ORDER BY ARTICLE, COLOR, SIZE, ORDER)
   AND COLOR = LAG(COLOR) OVER (ORDER BY ARTICLE, COLOR, SIZE, ORDER)
   AND SIZE = LAG(SIZE) OVER (ORDER BY ARTICLE, COLOR, SIZE, ORDER)
THEN 
(lag(STOCK) OVER (ORDER BY ARTICLE, COLOR, SIZE, ORDER))
-(lag(NEEDED) OVER (ORDER BY ARTICLE, COLOR, SIZE, ORDER))

ELSE STOCK
END
AS decimal(8, 2)) AS CALCULATEDSTOCK
.....

In the example there are three rows of ORDERS of same ARTICLE&COLOR&SIZE, but there could be more...

Many thanks for your patience and sweet greets!

See https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server/10309947#10309947. — APH, Apr 03 '19 at 22:54

score 0 · Accepted Answer · answered Apr 03 '19 at 23:05

I think you're missing PARTITION. Also, LAG is great, but if your just doing a type of running total, SUM with a bit of calculation would do just fine. First, you'll need an ID in your source data; duplicate columns will mess it up.

with source (ORDER_id,   ARTICLE,  COLOR ,   SIZE,   STOCK,   NEEDED) as
(
select 43002,   1000   ,  'GREY  ',   'L '  ,   13   ,   4 union all 
select 43002,   1000   ,  'GREY  ',   'XL'  ,   20   ,   5 union all 
select 43006,   1000   ,  'GREY  ',   'XL'  ,   20   ,   4 union all 
select 43012,   1000   ,  'GREY  ',   'XL'  ,   20   ,   6 union all 
select 43021,   1000   ,  'GREY  ',   'XL'  ,   20   ,   2 union all 
select 43021,   1000   ,  'PURPLE',   'M '  ,   7    ,   2 union all 
select 43023,   1000   ,  'PURPLE',   'L '  ,   6    ,   3 
)
select id, order_id, article, color, size, stock, NEEDED, stock + needed - sum(needed) over (partition by ARTICLE, COLOR, SIZE order by id)
from (
    select row_number() over (order by order_id) id, ORDER_id, ARTICLE, COLOR, SIZE, STOCK, NEEDED
    from source
) source_with_id

Feel so sorry, after a night of code I forgot to thank you. I feel ashamed, accept please my apologies. Both answers lead me to the result. — banshie, Apr 29 '19 at 10:24

score 0 · Answer 2 · answered Apr 03 '19 at 23:06

There may be a more elegant way to do this, but here's one option:

; with CTE as (select *
    , stock - sum(needed) over (partition by ARTICLE, color, size order by ORDER) as CalcNeeded
    , lead(ORDER) over (partition by ARTICLE, color, size order by ORDER) as PrevOrder
from MyTable)

select a.ORDER,
       a.ARTICLE,
       a.color,
       a.size,
       a.stock,
       a.needed,
       ISNULL(b.CalcNeeded, a.stock)
from cte a
left join cte b
on a.ARTICLE= b.ARTICLE and a.color = b.color and a.size = b.size
and a.ORDER = b.PrevOrder

Feel so sorry, after a night of code I forgot to thank you. I feel ashamed, accept please my apologies. Both answers lead me to the result. — banshie, Apr 29 '19 at 10:25

SQL SELECT with cumulative calculation across selected rows

2 Answers2