I have a problem with table's iteration. There is status product (test_product) in stock and actions: add or remove from stock.

In some day we have add or remove product, number of product and total number.

I want to count number of days, where total number is 0 in some period, let's say from 2023-01-01 to 2023-01-10 with starting value of total column is 0.

In my mind I have table like this:

Counting of 0 days is technical operation, it's not important now. Problem is building table.

I've tried something like this:

GO
DECLARE @PRODUCT AS VARCHAR(30)
SET @PRODUCT = 'test_product'
DECLARE @TOTAL AS INT
SET @TOTAL = 0
DECLARE @STOP_DATE AS DATE;
SET @STOP_DATE = CAST('2023-01-10' AS DATE)
DECLARE @UP_DATE AS DATE;
SET @UP_DATE = CAST('2023-01-01' AS DATE);
WHILE @STOP_DATE >= @UP_DATE
BEGIN  
SELECT
@UP_DATE AS calendar_day,
CASE
WHEN operation_date = @UP_DATE THEN operation_date
ELSE ''
END AS operation_date,
number,
@TOTAL number AS total
FROM stock
JOIN products ON products.id = stock.product_id
WHERE products.name = @PRODUCT
AND stock.operation_date >= @UP_DATE
AND stock.operation_date <= @UP_DATE
SET @UP_DATE = DATEADD(DAY, 1, @UP_DATE)
END

But I've got separation result, 7 of them are empty and it's too slow, because we have 9000 items for now.

Can you help me?

CodePudding user response：

You can accomplish to this task into three steps:

• build a calendar table with a recursive query, that stops at calen_date < '2023-10-01'
• left join the calendar table with your products table
• compute a running sum on your number value to get your total.

WITH calendar AS (
    SELECT CAST('2023-01-01' AS DATE) AS calendar_day
    UNION ALL 
    SELECT DATEADD(DAY, 1, calendar_day) FROM calendar WHERE calendar_day < '2023-01-10'
)
SELECT calendar_day, product, action_, operation_date, number, 
       SUM(COALESCE(number,0)) OVER(ORDER BY calendar_day) AS total
FROM      calendar     c
LEFT JOIN test_product p
       ON c.calendar_day = p.operation_date

Output:

Check the demo here.