Menu
Oracle SQL newbie here and first time poster.
I thought this would be simple until I realized I can't figure out how to split return assignments.
Here is my assignments 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've 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 makes sense. This is where return assignments are not separated by combined 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 I would like to see where the adjacent dates are combined per 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 a more season Oracle SQL programmer and he said I'd have to us PLSQL, but I'm thinking there has to be a way to make this work via 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');
796
Employee A has two rows for 07-01-2013 through 08-10-2014; I assumed that is a mistake and I deleted one of the rows.
Other than that, this is an application of the "tabibitosan method" for solving "gaps and islands" problems for date ranges. The trick is in creating the "groups" (gp) for adjacent 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
