Skip to Main Content
  • Questions
  • Deriving/Creating date span's from a set of begin & end dates.

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Linus.

Asked: September 02, 2017 - 1:27 pm UTC

Last updated: September 04, 2017 - 1:13 pm UTC

Version: 11.0

Viewed 1000+ times

You Asked

Hi, I am trying to write a query using analytical functions that derive or create date spans using a set of begin & end date. The eventual spans that gets created should have over-lapping spans. Attached LiveSQL link contains the test case (with sample data as well as the query that wrote). However, I am not very successful in generating the complete list of spans. Some or the other span is getting dropped out. Would need your help and suggestions to get it right. Below is the expected output for the sample data in the test case:

3353 8/1/2009  10/31/2009
3353 11/1/2009  10/31/2011
3353 1/1/2011  9/30/2013
3353 10/1/2013  6/30/2014
3353 7/1/2014  9/30/2014
3353 10/1/2014  2/28/2015
3353 3/1/2015  10/31/2016
3353 11/1/2016  12/31/9999
   
13221 8/1/2009  11/30/2014
13221 12/1/2014  2/28/2015
13221 3/1/2016  3/31/2016
13221 4/1/2016  12/31/9999



Thanks for your help.

Regards,
LR


with LiveSQL Test Case:

and Chris said...

So you want to take your begin+end ranges and, where there's an overlap, split this into separate start+end ranges?

I'm not sure what your logic is for deciding exactly what the start + end dates are. But here's something that may help you:

- First convert the start & ends into a single list of dates. You can do this with unpivot
- Get all the distinct dates from this
- Turn this back into start+end ranges by taking the current date and finding the next using lead.

Which gives something like:

drop table Emp_Dates;

create table Emp_Dates ( 
    Emp_Id varchar2(10), 
    Begin_Date date, 
    End_Date date 
);

Insert into Emp_Dates Values ('3353', to_date('3/1/2015','mm/dd/yyyy'),to_date('12/31/9999','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('8/1/2009','mm/dd/yyyy'),to_date('10/31/2009','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('8/1/2009','mm/dd/yyyy'),to_date('10/31/2016','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('8/1/2009','mm/dd/yyyy'),to_date('12/31/9999','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('7/1/2014','mm/dd/yyyy'),to_date('9/30/2014','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('1/1/2011','mm/dd/yyyy'),to_date('6/30/2014','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('10/1/2013','mm/dd/yyyy'),to_date('10/31/2016','mm/dd/yyyy'));
Insert into Emp_Dates Values ('3353', to_date('11/1/2009','mm/dd/yyyy'),to_date('9/30/2013','mm/dd/yyyy'));

Insert into Emp_Dates Values ('13221', to_date('8/1/2009','mm/dd/yyyy'),to_date('12/31/9999','mm/dd/yyyy'));
Insert into Emp_Dates Values ('13221', to_date('12/1/2014','mm/dd/yyyy'),to_date('2/28/2015','mm/dd/yyyy'));
Insert into Emp_Dates Values ('13221', to_date('8/1/2009','mm/dd/yyyy'),to_date('3/31/2016','mm/dd/yyyy'));

with dts as (
  select * from emp_dates
  unpivot ( dt for src in (begin_date, end_date) )
) , distinct_dts as(
  select distinct emp_id, dt from dts
) , ranges as (
  select emp_id, dt start_date, 
         lead(dt) over (partition by emp_id order by dt) end_date
  from   distinct_dts
)
  select * from ranges
  where  end_date is not null
  order  by emp_id desc, start_date;

EMP_ID  START_DATE            END_DATE              
3353    01-AUG-2009 00:00:00  31-OCT-2009 00:00:00  
3353    31-OCT-2009 00:00:00  01-NOV-2009 00:00:00  
3353    01-NOV-2009 00:00:00  01-JAN-2011 00:00:00  
3353    01-JAN-2011 00:00:00  30-SEP-2013 00:00:00  
3353    30-SEP-2013 00:00:00  01-OCT-2013 00:00:00  
3353    01-OCT-2013 00:00:00  30-JUN-2014 00:00:00  
3353    30-JUN-2014 00:00:00  01-JUL-2014 00:00:00  
3353    01-JUL-2014 00:00:00  30-SEP-2014 00:00:00  
3353    30-SEP-2014 00:00:00  01-MAR-2015 00:00:00  
3353    01-MAR-2015 00:00:00  31-OCT-2016 00:00:00  
3353    31-OCT-2016 00:00:00  31-DEC-9999 00:00:00  
13221   01-AUG-2009 00:00:00  01-DEC-2014 00:00:00  
13221   01-DEC-2014 00:00:00  28-FEB-2015 00:00:00  
13221   28-FEB-2015 00:00:00  31-MAR-2016 00:00:00  
13221   31-MAR-2016 00:00:00  31-DEC-9999 00:00:00


This gives ranges where the previous end = current start (i.e. proper ranges! ;) You can jigger this by subtracting from the end dates if necessary.

If you're still stuck, please give a detailed (non-code) explanation of how you determine what makes a new start+end range.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.