SQL Query in Snowflake - Calculate Days of Supply

Question

I tried to find the days of supply each day. For example, date 1/23/2025 - It has inventory 700 which already subtract the forecast, 400 on that day. Count how many date of forecast can be covered by 700. 700 <= (200+300+100) equal 3 days on 1/23/2025. 600 <= (300+100) equal 2 days

Data table

Location	Material	Start_Date	Inventory	Forecast
W01	123456	1/23/2025	700	400
W01	123456	1/24/2025	600	200
W01	123456	1/25/2025	400	300
W01	123456	1/26/2025	450	100
W01	123456	1/27/2025	50	300

Desired Output

Location	Material	Start_Date	Inventory	Forecast	DOS
W01	123456	1/23/2025	700	400	3
W01	123456	1/24/2025	600	200	2
W01	123456	1/25/2025	400	300	2
W01	123456	1/26/2025	450	100	1
W01	123456	1/27/2025	50	300	0

My query doesn't work

SELECT 
    "A"."LOCATION",
    "A"."MATERIAL", 
    "A"."START_DATE", 
    "A"."INVENTORY", 
    "A"."FORECAST",

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

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

END AS "DOS"

FROM "A" 
ORDER BY 
    "A"."START_DATE"

Answer 1

In your output, for 1/26/2025 DOS should not be 2, instead it should be 1 as per your explanation that the current row forecast should not be included.So if inventory is 450 on 1/26/2025 then only 300 should be included which is 1 day.

I have approached it with a recursion where the anchor is set at 0 DOS and then each row for the subsequent dates are compared until the sum of forecast becomes >= than the inventory.

Finally in the outer query all columns are fetched alongside DOS by joining DOS_CTE and the table.

WITH RECURSIVE dos_cte AS (
  SELECT 
    location,
    Material,
    start_date,
    inventory,
    forecast,
    0 AS DOS,
    inventory AS remaining_inventory,
    start_date AS initial_date
  FROM 
    test
  
  UNION ALL
  
  SELECT 
    a.location,
    a.material,
    a.start_date,
    a.inventory,
    a.forecast,
    cte.dos + 1 AS DOS,
    cte.remaining_inventory - a.forecast AS remaining_inventory,
    cte.initial_date
  FROM 
    test a
  JOIN 
    DOS_CTE cte
  ON 
    a.location = cte.location
    AND a.material = cte.material
    AND a.start_date > cte.start_date 
  WHERE 
    cte.remaining_inventory - a.forecast >= 0
)

SELECT 
  t.location,
  t.material,
  t.start_date,
  t.inventory,
  t.forecast,
  COALESCE(MAX(cte.DOS) , 0) AS DOS
FROM 
  test t
LEFT JOIN 
  DOS_CTE cte
ON 
  t.location = cte.location
  AND t.material = cte.material
  AND t.start_date = cte.initial_date
GROUP BY 
  t.location,
  t.material,
  t.start_date,
  t.inventory,
  t.forecast
ORDER BY 
  t.start_date;