Merge Date Ranges

Question

Oracle SQL is new here and the first poster.

I thought it would be simple until I realized that I could not figure out how to split return destinations.

Here is my assignment table: ASGN

ID ST_DT END_DT POS LOCN STATUS WAGE_CD -- ---------- ---------- ----- ---- ------ ------- A 12-31-2006 08-16-2009 CLERK LAX 3 A A 08-17-2009 10-04-2009 CLERK LAX 0 Z A 10-05-2009 06-30-2010 OPR NYC 3 A A 07-01-2010 12-31-2010 OPR NYC 3 B A 01-01-2011 06-30-2012 OPR NYC 3 C A 07-01-2012 04-09-2013 OPR NYC 3 D A 04-10-2013 06-30-2013 CLERK LAX 3 A A 07-01-2013 08-10-2014 CLERK LAX 3 B A 07-01-2013 08-10-2014 CLERK LAX 3 C B 04-10-2013 05-31-2013 SUP LAX 3 A B 06-01-2013 06-30-2014 SUP LAX 0 Z B 07-01-2013 08-10-2014 SUP LAX 3 B B 08-11-2014 08-11-2014 CLERK NYC 3 A B 08-12-2014 01-11-2015 SUP LAX 3 A B 01-12-2015 02-10-2016 SUP LAX 3 B B 02-11-2016 08-12-2016 OPER SFO 3 A B 02-11-2016 08-12-2016 OPER SFO 3 B 

I have already tried this below with unexpected results.

 SELECT * FROM ( SELECT ID ,MIN(ST_DT) ST_DT ,MAX(END_DT) END_DT ,POS ,LOCN ,STATUS FROM ASGN GROUP BY ID, LOCN, POS, STATUS ) SUBQRY ORDER BY ID, ST_DT 

Unexpected results, but it makes sense. Here, return assignments are not shared with previous assignments.

 ID ST_DT END_DT POS LOCN STATUS -- ---------- ---------- ----- ---- ------ A 12-31-2006 08-10-2014 CLERK LAX 3 A 08-17-2009 10-04-2009 CLERK LAX 0 A 10-05-2009 04-09-2010 OPR NYC 3 B 04-10-2013 02-10-2015 SUP LAX 3 B 06-01-2013 06-30-2014 SUP LAX 0 B 08-11-2014 08-11-2014 CLERK NYC 3 B 02-11-2016 08-12-2016 OPER SFO 3 

And the results that I would like to see when neighboring dates are combined for each ID, POSition, LOCatioN and STATUS:

 ID ST_DT END_DT POS LOCN STATUS -- ---------- ---------- ----- ---- ------ A 12-31-2006 08-16-2009 CLERK LAX 3 A 08-17-2009 10-04-2009 CLERK LAX 0 A 10-05-2009 04-09-2010 OPR NYC 3 A 04-10-2013 08-10-2014 CLERK LAX 3 B 04-10-2013 05-31-2013 SUP LAX 3 B 06-01-2013 06-30-2014 SUP LAX 0 B 07-01-2013 08-10-2014 SUP LAX 3 B 08-11-2014 08-11-2014 CLERK NYC 3 B 08-12-2014 02-10-2015 SUP LAX 3 B 02-11-2016 08-12-2016 OPER SFO 3 

I asked the Oracle SQL programmer for more time, and he said that I would have to PLSQL, but I think there should be a way to make this work through SQL.

Test setup script:

 create table asgn (id varchar2(10) ,st_dt date ,end_dt date ,pos varchar2(10) ,locn varchar2(10) ,status number ,wage_cd varchar2(10)); insert into asgn values('A',to_date('12-31-2006','mm-dd-yyyy'),to_date('08-16-2009','mm-dd-yyyy'),'CLERK','LAX',3,'A'); insert into asgn values('A',to_date('08-17-2009','mm-dd-yyyy'),to_date('10-04-2009','mm-dd-yyyy'),'CLERK','LAX',0,'Z'); insert into asgn values('A',to_date('10-05-2009','mm-dd-yyyy'),to_date('06-30-2010','mm-dd-yyyy'),'OPR','NYC',3,'A'); insert into asgn values('A',to_date('07-01-2010','mm-dd-yyyy'),to_date('12-31-2010','mm-dd-yyyy'),'OPR','NYC',3,'B'); insert into asgn values('A',to_date('01-01-2011','mm-dd-yyyy'),to_date('06-30-2012','mm-dd-yyyy'),'OPR','NYC',3,'C'); insert into asgn values('A',to_date('07-01-2012','mm-dd-yyyy'),to_date('04-09-2013','mm-dd-yyyy'),'OPR','NYC',3,'D'); insert into asgn values('A',to_date('04-10-2013','mm-dd-yyyy'),to_date('06-30-2013','mm-dd-yyyy'),'CLERK','LAX',3,'A'); insert into asgn values('A',to_date('07-01-2013','mm-dd-yyyy'),to_date('08-10-2014','mm-dd-yyyy'),'CLERK','LAX',3,'B'); insert into asgn values('A',to_date('07-01-2013','mm-dd-yyyy'),to_date('08-10-2014','mm-dd-yyyy'),'CLERK','LAX',3,'C'); insert into asgn values('B',to_date('04-10-2013','mm-dd-yyyy'),to_date('05-31-2013','mm-dd-yyyy'),'SUP','LAX',3,'A'); insert into asgn values('B',to_date('06-01-2013','mm-dd-yyyy'),to_date('06-30-2014','mm-dd-yyyy'),'SUP','LAX',0,'Z'); insert into asgn values('B',to_date('07-01-2013','mm-dd-yyyy'),to_date('08-10-2014','mm-dd-yyyy'),'SUP','LAX',3,'B'); insert into asgn values('B',to_date('08-11-2014','mm-dd-yyyy'),to_date('08-11-2014','mm-dd-yyyy'),'CLERK','NYC',3,'A'); insert into asgn values('B',to_date('08-12-2014','mm-dd-yyyy'),to_date('01-11-2015','mm-dd-yyyy'),'SUP','LAX',3,'A'); insert into asgn values('B',to_date('01-12-2015','mm-dd-yyyy'),to_date('02-10-2016','mm-dd-yyyy'),'SUP','LAX',3,'B'); insert into asgn values('B',to_date('02-11-2016','mm-dd-yyyy'),to_date('08-12-2016','mm-dd-yyyy'),'OPER','SFO',3,'A'); insert into asgn values('B',to_date('02-11-2016','mm-dd-yyyy'),to_date('08-12-2016','mm-dd-yyyy'),'OPER','SFO',3,'B'); 

Answer 1

Employee A has two lines for 07-01-2013 to 08-10-2014; I assumed that this is a mistake and I deleted one of the lines.

In addition, this is a tabibitosan method application for resolving gaps and islands problems for date ranges. The trick is to create “groups” ( gp ) for contiguous intervals.

 with prep ( id, st_dt, end_dt, gp, pos, locn, status ) as ( select id, st_dt, end_dt, end_dt - sum( end_dt - st_dt + 1 ) over (partition by id, pos, locn, status order by st_dt), pos, locn, status from asgn ) select id, min(st_dt) as st_dt, max(end_dt) as end_dt, pos, locn, status from prep group by id, gp, pos, locn, status order by id, st_dt ; ID ST_DT END_DT POS LOCN STATUS ---------- ---------- ---------- ---------- ---------- ---------- A 12-31-2006 08-16-2009 CLERK LAX 3 A 08-17-2009 10-04-2009 CLERK LAX 0 A 10-05-2009 04-09-2013 OPR NYC 3 A 04-10-2013 08-10-2014 CLERK LAX 3 B 04-10-2013 05-31-2013 SUP LAX 3 B 06-01-2013 06-30-2014 SUP LAX 0 B 07-01-2013 08-10-2014 SUP LAX 3 B 08-11-2014 08-11-2014 CLERK NYC 3 B 08-12-2014 02-10-2016 SUP LAX 3 B 02-11-2016 08-12-2016 OPER SFO 3 10 rows selected