Creating dummy row and Date gaps

Analytics
Enthusiast

Creating dummy row and Date gaps

All here is my data looks like:

ID --------  Begin Dt --------- End Dt

1              Jan 1, 2016        Jan 1, 2017

1             Jan 1, 2017        Dec 30, 2018

 

I want the start of Begin Dt to be 01-01-1900 and continue with the dates I have and end with 12-31-3001 so the results should be like this:

1           01-01-1900               Jan 1, 2016

1           Jan 1, 2016              Jan 1, 2017

1           Jan 1, 2017              Dec 30, 2018

1           Dec 30, 2018           12-31-3001 

Can this be done w recursive? or simple derived table?

Thanks!

 

1 REPLY
Junior Contributor

Re: Creating dummy row and Date gaps

 

select id, begin_dt, end_dt
from tab
UNION ALL
select id, date '1900-01-01', min(begin_dt)
from tab
group by 1
UNION ALL
select id, max(end_dt), date '3001-12-31'
from tab
group by 1