Expand Date Column snowflake

user_11162361 0
Viewed 44

I have two date columns like these:

date_col_1     date_col_2
2020-02-01     2023-01-01   
2020-03-01     2023-02-01
2020-04-01     .
.              .
.              .
.              .
2022-12-01     2023-12-01

What I want is whenever date_col_2 > max(date_col_1), all the values greater than max(date_col_1) should go under the last value of the date_col_1 like this,

date_col_1     
2020-02-01      
2020-03-01     
2020-04-01     
.              
.              
.              
2022-12-01
Append here
2023-01-01
2023-02-01
2023-03-01
.
.
.

I have tried various ways, nothing is giving the desired result. TIA

snowflake-cloud-data-platform sql
1 Answers
WITH fake_data AS (
    SELECT 
        TO_DATE(date_col_1,'yyyy-mm-dd') as date_col_1
        ,TO_DATE(date_col_2,'yyyy-mm-dd') as date_col_2
    FROM VALUES
        ('2020-02-01','2023-01-01'),  
        ('2020-03-01','2023-02-01'),
        ('2022-12-01','2023-12-01')
        v(date_col_1, date_col_2)
), massive_date_set as (
    SELECT 
        dateadd('day', row_number()over(order by null), TO_DATE('2000-01-01','yyyy-mm-dd')) AS date
    FROM TABLE(GENERATOR(ROWCOUNT => 356 * 30))
), min_max AS (
    SELECT 
        min(date_col_1) AS date_from
        ,greatest(max(date_col_1), max(date_col_2)) AS date_to
    FROM fake_data
)
SELECT d.date
FROM  min_max AS mm
JOIN massive_date_set d
    ON d.date BETWEEN mm.date_from AND mm.date_to
ORDER BY 1;

gives:

DATE
2020-02-01
2020-02-02
2020-02-03
2020-02-04
2020-02-05
massive cut
2023-11-28
2023-11-29
2023-11-30
2023-12-01
user_439920
Related