Select Date1, Date2 From Table while in Redshift

Question

What is the right syntax for creating a transaction base on date range.

for ex. this is my date set

Table

DocID     Date1           Date2 

0001     2020-01-01      2020-01-03

and this is what i want to achieve since (Date1)-(Date2) = 2 that's why we have 2020-01-02 and 2020-01-03 in dateref and uses the same DocID

DocID     Date1          Date2           DateRef 

0001     2020-01-01     2020-01-03      2020-01-02 

0001     2020-01-01     2020-01-03      2020-01-03

Thanks

hmmm i just want to create a line item base on the date1 and date2, ex if you notice on the table that i want to achieve the DocID already got 2 entry which is for 2020-01-02 and 2020-01-03 — Ren
Your sample data is difficult to understand. Please add more examples to your Table and your Output. — John Rotenstein

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-03-17T11:45:55

Doing this in SQL generally requires a numbers table or recursive CTE -- neither of which Redshift directly supports.

Assuming you have a table big enough for the rows you need, you can use:

with n as (
      select row_number() over (order by docid) as n
      from t
     )
select t.*,
       (t.date1 + n * interval '1 day') as date_ref
from t join
     n
     on t.date1 + n * interval '1 day' <= t.date2

Select Date1, Date2 From Table while in Redshift

1 Answers