SQL Query in Snowflake - Calculate Days of Supply each date inventory with forecast each day

Question

I would like to create a column to calculate days of supply on each date inventory and the day and after forecast.

In the example data, 1/23/2025 has 1000 inventory, count how many days of forecast will be subtracted until before negative. Sum of forecasts from 1/23/2025 to 1/25/2025 is 700 equal to 3 days. Therefore, it is 3 days of supply on 1/23/2025

"FUTURE_DEMAND" just sum of all demand but I need to just sum the forecast when Projected_on_hand - sum(total_demand) is larger than 0

SELECT 
"A"."LOCATION",
"A"."MATERIAL", 
"A"."START_DATE", 
"A"."PROJECTED_ON_HAND", 
"A"."TOTAL_DEMAND",


(SELECT SUM("B"."TOTAL_DEMAND")
FROM "B"
WHERE "B"."START_DATE" >= "A"."START_DATE" ) AS "FUTURE_DEMAND",

CASE
    WHEN (SELECT SUM("B"."TOTAL_DEMAND")
        FROM "B"
        WHERE "B"."START_DATE" >= "A"."START_DATE ) = 0 THEN NULL
    ELSE "A"."PROJECTED_ON_HAND" /
        (SELECT SUM ("B"."TOTAL_DEMAND")
            FROM "B"
            WHERE "B"."START_DATE" >= "A"."START_DATE 

END AS "DOS"


FROM "A" 


ORDER BY 
"A"."START_DATE"

I am trying to calculate the days of supply each date with daily forecast today and after

Data Table

Location	Material	Start_Date	Projected_Inventory	Forecast
W01	123456	1/23/2025	1000	400
W01	123456	1/24/2025	600	100
W01	123456	1/25/2025	500	200
W01	123456	1/26/2025	450	400
W01	123456	1/27/2025	50	100

Expect Result - New column for DOC calculation

Location	Material	Start_Date	Projected_Inventory	Forecast	DOC
W01	123456	1/23/2025	1000	400	3
W01	123456	1/24/2025	600	100	2
W01	123456	1/25/2025	500	200	1
W01	123456	1/26/2025	450	400	1
W01	123456	1/27/2025	50	100	0

Answer 1

As part of rank_cumul_cte CTE all the rows are ranked and cumulative forecast for each row is calculated

SELECT location,material, start_date,forecast,projected_inventory,
ROW_NUMBER() OVER (PARTITION BY location,material ORDER BY start_date) AS rn,

SUM(forecast) OVER (PARTITION BY location,material ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_forecast

FROM test

which outputs as

Then this CTE is self joined based on each row getting compared with itself or higher ranks(for example 2025-01-23 getting compared with itself(since we need to count the number of days) and all dates higher than it).

There is one more condition to compare that projected_inventory for the row should be higher than the net cumulative forecast

a.projected_inventory - (b.cumulative_forecast - a.cumulative_forecast + a.forecast) >= 0

Another condition is to check if the forecast for a date is less than inventory then DOC should be 0

CASE 
    WHEN a.projected_inventory < a.forecast THEN 0

Final Query

WITH rank_cumul_cte AS (
SELECT location,material, start_date,forecast,projected_inventory,
ROW_NUMBER() OVER (PARTITION BY location,material ORDER BY start_date) AS rn,
SUM(forecast) OVER (PARTITION BY location,material ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_forecast
FROM test
)
SELECT a.location,a.material,a.start_date,a.projected_inventory,a.forecast,
CASE 
    WHEN a.projected_inventory < a.forecast THEN 0
    ELSE COUNT(b.start_date)
END AS DOC
FROM  rank_cumul_cte a
LEFT JOIN
rank_cumul_cte b
ON
a.location = b.location
AND a.material = b.material
AND b.rn >= a.rn
AND a.projected_inventory - (b.cumulative_forecast - a.cumulative_forecast + a.forecast) >= 0
    -- 
GROUP BY
a.location,a.material, a.start_date, a.projected_inventory, a.forecast
ORDER BY
    a.start_date;

Output