CODEWITHSUNDEEP
snowflake-cloud-data-platform
Thomas
Thomas

Reputation: 13

Creating rows in a table based on min and max date in Snowflake SQL

Is there a relatively simple way to create rows in a table based on a range of dates?

For example; given:

ID Date_min Date_max
1 2022-02-01 2022-20-05
2 2022-02-09 2022-02-12

I want to output:

ID Date_in_Range
1 2022-02-01
1 2022-02-02
1 2022-02-03
1 2022-02-04
1 2022-02-05
2 2022-02-09
2 2022-02-10
2 2022-02-11
2 2022-02-12

I saw a solution when the range is integer based (How to create rows based on the range of all values between min and max in Snowflake (SQL)?)

But in order to use that approach GENERATOR(ROWCOUNT => 1000) I have to convert my dates to integers and back, and it just gets very messy very quick, especially since I need to apply this to millions of rows.

So, I was wondering if there is a simpler way to do it when dealing with dates instead of integers? Any hints anyone can provide?

Upvotes: 1

Views: 890

Answers (2)

Pankaj
Pankaj

Reputation: 2746

Another one without using generator -


with data (ID,Date_min,Date_max) as (
select * from values
(1,to_date('2022-02-01','YYYY-DD-MM'),to_date('2022-20-05','YYYY-DD-MM')),
(2,to_date('2022-02-09','YYYY-DD-MM'),to_date('2022-02-12','YYYY-DD-MM'))
)
select id,
Date_min,
Date_max,
dateadd(day, index, Date_min) day_slots from data,
table(split_to_table(repeat(',',datediff(day, Date_min, Date_max)-1),','));

SQL with first date -

with data (ID,Date_min,Date_max) as (
select * from values
(1,to_date('2022-02-01','YYYY-DD-MM'),to_date('2022-20-05','YYYY-DD-MM')),
(2,to_date('2022-02-09','YYYY-DD-MM'),to_date('2022-02-12','YYYY-DD-MM'))
)
select id,
dateadd(month, index-1, Date_min) day_slots from data,
table(split_to_table(repeat(',',datediff(month, Date_min, Date_max)),','));

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

But in order to use that approach GENERATOR(ROWCOUNT => 1000) I have to convert my dates to integers and back, and it just gets very messy very quick, especially since I need to apply this to millions of rows.

There is no need to convert date to int back and forth, just simple DATEADD('day', num, start_date)

Pseudocode:

WITH sample_data(id, date_min, date_max) AS (
   SELECT 1, '2022-02-01'::DATE, '2022-02-05'::DATE 
   UNION 
   SELECT 2, '2022-02-09'::DATE, '2022-02-12'::DATE
) , numbers AS (
  SELECT ROW_NUMBER() OVER(ORDER BY SEQ4())-1 AS num -- 0 based
  FROM TABLE(GENERATOR(ROWCOUNT => 1000)) -- should match max anticipated span
)
SELECT s.id, DATEADD(DAY, n.num, s.date_min) AS calculated_date
FROM sample_data AS s
JOIN numbers AS n
 ON DATEADD('DAY', n.num, s.date_min) BETWEEN s.date_min AND s.date_max
ORDER BY s.id, calculated_date;

Ouptut:

enter image description here

Upvotes: 0

Related Questions