SQL - Building iteration table

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

product	action	operation_date	number	total
test_product	add	2023-01-03	4	4
test_product	remove	2023-01-05	-3	1
test_product	remove	2023-01-07	-1	0

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:

calendar_day	product	action	operation_day	number	total
2023-01-01					0
2023-01-02					0
2023-01-03	test_product	add	2023-01-03	4	4
2023-01-04					4
2023-01-05	test_product	remove	2023-01-05	-3	1
2023-01-06					1
2023-01-07	test_product	remove	2023-01-07	-1	0
2023-01-08					0
2023-01-09					0
2023-01-10					0

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?

Solution

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:

calendar_day	product	action	operation_day	number	total
2023-01-01					0
2023-01-02					0
2023-01-03	test_product	add	2023-01-03	4	4
2023-01-04					4
2023-01-05	test_product	remove	2023-01-05	-3	1
2023-01-06					1
2023-01-07	test_product	remove	2023-01-07	-1	0
2023-01-08					0
2023-01-09					0
2023-01-10					0

Check the demo here.