Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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
This solution is work.
It was very thoughtful of you.