Generate rows based on start- and end date

Archive
1

I’m trying to generate multiple rows based on the start- and end date of each row. Besides, an interval is given that indicates whether the row should be repeated on a daily or weekly basis. The best way to illustrate my question is using an example. Below is an example of the input table;

Id Start Date End Date Interval
1 2022-06-01 2022-06-03 Daily
2 2022-06-05 2022-06-06 Daily
3 2022-06-01 2022-06-15 Weekly

The desired output given the input table;

Id Date
1 2022-06-01
1 2022-06-02
1 2022-06-03
2 2022-06-05
2 2022-06-06
3 2022-06-01
3 2022-06-08
3 2022-06-15

Could anyone provide some help or point me in the right direction? Thanks in advance!

2

Already found the solution myself. I expected that I would need to use Macros, but instead I was able to solve the question using plain SQL.

The solution for Postgres;

/* Postgres example */
CREATE TEMPORARY TABLE example_tbl (id int, start_date date, end_date date, frequency varchar);
INSERT INTO example_tbl
  SELECT 1, '2022-06-01'::date, '2022-06-03'::date, 'Daily'
  UNION ALL SELECT 2, '2022-06-05'::date, '2022-06-06'::date, 'Daily'
  UNION ALL SELECT 3, '2022-06-01'::date, '2022-06-15'::date, 'Weekly';

WITH extended_tbl AS(
    SELECT 
        *,
        CASE 
            WHEN frequency = 'Daily' THEN '1 day' 
            WHEN frequency = 'Weekly' THEN '7 day'
            ELSE NULL
        END::interval AS interval
    FROM example_tbl
)

SELECT 
    extended_tbl.id,
    GENERATE_SERIES(start_date, end_date, interval) AS generated_date
FROM extended_tbl
1 Like