I am using the query in Snowflake:

select DATEDIFF(day,start_date ,end_date) as days
       ,start_date
       ,end_date 
from table1

It gives me no. of days as:

but I need to find the dates for the days instead of just the no. of days i.e I want to see those all 14 dates instead of just no. of days.

can anyone help.

CodePudding user response：

You can generate a derived table that holds all dates in the overall date range needed for this table and then use that to your join your table back in:

WITH CTE_MY_DATE AS (
    SELECT DATEADD(DAY, SEQ4(), '2000-01-01') AS MY_DATE
    FROM TABLE(GENERATOR(ROWCOUNT=>10000))  
    WHERE MY_DATE BETWEEN 
        (SELECT MIN(start_date) FROM table1)
        AND 
        (SELECT MAX(end_date) FROM table1)
)
SELECT nct.calendar_date_column
   start_date, end_date
FROM new_calendar_table nct
   INNER JOIN table1 
      ON nct.calendar_date_column BETWEEN table1.start_date and table1.end_date

CodePudding user response：

The dates could be generated by multiplying rows per difference of days:

SELECT table1.start_date
      ,table1.end_date
      ,table1.start_date   ROW_NUMBER() OVER(PARTITION BY table1.start_date,
                                                          table1.end_date 
                                             ORDER BY NULL)-1 AS generated_date
FROM table1
, TABLE(SPLIT_TO_TABLE(SPACE(DATEDIFF('day', table1.start_date, table1.end_date))
        , ' ')) AS r;

For sample data:

CREATE OR REPLACE TABLE table1
AS
SELECT '2022-09-03'::DATE AS start_date, '2022-09-17'::DATE AS end_date UNION ALL
SELECT '2022-08-19'::DATE, '2022-09-16'::DATE;

Output (part):