Is it possible to increment an Column in MS SQL inside a WITH and CASE WHEN statement.

[namemon]

Is it possible to increment an Column : Stock_product in MS SQL inside a WITH and CASE WHEN statement.

I am attempting to sum amount products per day (Column : sum_Per_Day) in my table to get Column : Stock_product (Desired results) but I get Stock_product and the result  that running from old order number and it wasn’t what I had hoped for.

My code:

WITH SourceD_ AS (
SELECT b.Orders
,b.shop_no
,b.d_Date
,b.product_no
,a.product_name
,b.Chk_Order
,b.sum_Per_Day
,lag(b.Chk_Order) OVER (ORDER by b.Chk_Order , b.Chk_date) AS Prev_Chk_Order
,lag(b.sum_Per_Day) OVER (ORDER by b.Chk_Order , b.Chk_date) AS Prev_sum_Per_Day
,ROW_NUMBER() OVER (ORDER BY b.Chk_Order , b.Chk_date) AS rn
FROM db_datatest b LEFT JOIN db_Mastertest a ON (b.product_no = a.product_no)
WHERE b.d_Date > getdate()-374)
),
SumD_ AS (
SELECT 
*,
SUM(CASE
WHEN (Chk_Order != Prev_Chk_Order) THEN sum_Per_Day
WHEN (ISNULL(sum_Per_Day,0) != 0) THEN sum_Per_Day
WHEN (ISNULL(sum_Per_Day,0) = 0) THEN 0
ELSE 0
END) OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Stock_product
FROM SourceD_
)
SELECT 
*
FROM SumD_
ORDER BY rn;

I want the following result Column : Stock_product (Desired results)
by If the orders number changes (new orders number) should use value from column : Stock_product calculate on base column : sum_Per_Day to create new value of column : Stock_product

Best regard.

[Musadev]

Hi @namemon 

Has your query been resolved now?

If yes then mark the first reply as a Solution. It will help the new members. Thanks

[Musadev · Accepted solution]

Hi @namemon 

You need to get the running sum for each order and the order should be on the date, right?

Try to use Windows function and partition the data based on the order and date, the date should be in ascending order. 

Please share sample data as mentioned in the screenshot, I will give a shot to it. Thanks

[namemon]

This solution is work.

It was very thoughtful of you.