Search code examples
sqloracle-databaselaglead

Creating start and end dates

I have the below table

 Createdt    Updatedt    id
 1/1/2019    3/1/2019    1
 1/1/2019    5/1/2019    1
 1/1/2019    7/1/2019    1

Expected results:

Createdt    Start_dt_cycle    End_dt_cycle  id
1/1/2019    1/1/2019          2/28/2019     1 
1/1/2019    3/1/2019          4/30/2019     1
1/1/2019    5/1/2019          06/30/2019    1
1/1/2019    7/1/2019          12/31/9999    1

My results:

Createdt    Start_dt_cycle    End_dt_cycle  id
1/1/2019    3/1/2019          4/30/2019     1
1/1/2019    5/1/2019          06/30/2019    1
1/1/2019    7/1/2019          12/31/9999    1

I used the LEAD function to capture the desired end_dt. I'm using the updatedt to create the cycles but I really want the cycles to start on the createdt and not the first update.

Solution

  • You seem to want union all and lead():

    select id, start_dt_cycle,
       lead(start_dt_cycle, 1, date '9999-12-31') over (partition by id order by start_dt_cycle) as end_dt_cycle
from ((select min(createddt) as start_dt_cycle, id
       from t
       group by id
      ) union all
      (select updatedt, id
       from t
      )
     ) t