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!
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