SQL Query in Snowflake - Calculate Days of Supply - Stack Overflow

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

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"

• sql
• snowflake-cloud-data-platform
• case
• cumulative-sum

Share Follow edited Jan 31 at 20:23 Yu Ching Tsoi asked Jan 31 at 2:50 Yu Ching TsoiYu Ching Tsoi 2544 bronze badges 2

• Is your "Output" your desired output or your query output? – Dale K Commented Jan 31 at 2:57
• @DaleK I think this question is very similar to OP's previous question. The previous one was to include forecast for the current date while this one is to exclude the forecast for current date – samhita Commented Jan 31 at 17:25

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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;