I have a snowflake table that looks like this.

I need to explode/flatten on the start_date and end_date to get an output that looks like this.

Any ideas/recommendations on how to do this using a snowflake task? I'm fairly new to snowflake/sql.

CodePudding user response：

Using generated calendar and BETWEEN AND:

WITH calendar AS (
  SELECT DATEADD(day, ROW_NUMBER() OVER(ORDER BY seq8()), '1999-12-31'::DATE) AS d
  FROM TABLE(GENERATOR(ROWCOUNT => 365*100))
)
SELECT t.id, c.d
FROM calendar c
JOIN t
  ON c.d BETWEEN t.start_date AND t.end_date
ORDER BY t.id, c.d;

Output:

CodePudding user response：

I use the UDF to get the list, this avoids the need to generate a calendar table, but with a bit more coding.

UDF:

create or replace function date_list(start_date string, diff float)
returns string
language javascript
as 
$$
    function get_date_number(num) {
        if(num < 10) {
            return '0'   num;
        }
        
        return num;
    }
    
    retVal = START_DATE;
    if(DIFF > 0) {
        for(i=1; i<=DIFF; i  ) {
            var date = new Date(START_DATE);
            date.setDate(date.getDate()   i);
            retVal  = ','   
                      date.getFullYear()   '-'   
                      (get_date_number(date.getMonth() 1))   '-'   
                      get_date_number(date.getDate());
        }
    }
    
    return retVal;
$$;

Then run the query:

with t as (
    select 
        id, 
        strtok_to_array(
            date_list(
                start_date, 
                end_date - start_date
            ), 
            ','
        ) as date_list 
    from test
)
select id, f.value::string as date from t,
lateral flatten(input => t.date_list) f;

The result:

 ---- ------------ 
| ID | DATE       |
|---- ------------|
|  1 | 2021-05-10 |
|  1 | 2021-05-11 |
|  1 | 2021-05-12 |
|  2 | 2021-07-05 |
|  2 | 2021-07-06 |
|  2 | 2021-07-07 |
|  2 | 2021-07-08 |
|  2 | 2021-07-09 |
 ---- ------------