SQL Question: Find overlap in periods – using Analytical Functions (LAG and LEAD)
Today one of my colleagues asked me “What again was your query for finding overlaps in a table with periods, demarcated by start_date and end_date. I think he was referring to the Oracle 7Up Workshop that we run every two months or so. In that workshop, we do a substantial section on Oracle’s Analytical Functions. Finding potential overlaps between different records in the same table sounds like an excellent candidate for useful application of Analytical Functions. However, I did not have the query ready, just like that.
In a previous post, Introducing Materialized Views as mechanism for Business Rule implementation (complex declarative constraints), I have discussed how Materialized Views can be used to declaratively implement constraints that span multiple records or even tables. That discussion is also close to the question at hand – it even includes an example of Date Periods, Project Allocations that must fall between the Start Date and End Date of the Project – but it does not provide a ready answer to AndrÃ©’s question.
So let’s see what we can do. I will use the same example: table PROJECTS and table PROJECT_ASSIGNMENTS:
create table projects ( projno int primary key , name varchar2(100) , start_date date not null , end_date date not null ) / create table project_assignments ( empno int not null , projno int not null , start_date date not null , end_date date not null ) /
and let’s create some data in those tables:
insert into projects values (1,'Migration to Oracle 10gR2','01-jan-2005','30-jun-2005') / insert into projects values (2,'Redevelopment of Core Application in Oracle ADF Technology','01-jun-2005','31-dec-2005') / insert into project_assignments values (1,2,'15-apr-2005','29-may-2005') / insert into project_assignments values (1,1,'01-aug-2005','31-dec-2005') / insert into project_assignments values (2,1,'01-jun-2005','31-jul-2005') / insert into project_assignments values (1,1,'01-sep-2005','31-oct-2005') / insert into project_assignments values (1,2,'15-jun-2005','29-jun-2005') /
Note that for the example the data in PROJECTS is not even required.
Now we could wonder whether perhaps we have double allocated the same person. In other words: do the assignments for any employee overlap? What assignments have we created where – for the same employee – two assignments fall on the same day(s).
This calls for Analytics. We will PARTITION BY employee, as we need to find overlap within the assignments of a single employee. We want to compare each assignment with its predecessor and its successor: does the assignment start before the preceding assignment ends? Or does the assignment continue while the next one has already started? Well, we do not actually have to look at both cases: just looking at each assignment and its next assignment will cover the entire set. We will use LEAD to look from an assignment to the next assignment and we will ORDER the assignments by start_date.
The in-line query we use:
select lead(start_date,1) over (partition by empno order by start_date) next_stint , start_date , end_date , projno , empno from project_assignments / NEXT_STIN START_DAT END_DATE PROJNO EMPNO --------- --------- --------- ---------- ---------- 15-JUN-05 15-APR-05 29-MAY-05 2 1 01-AUG-05 15-JUN-05 29-JUN-05 2 1 01-SEP-05 01-AUG-05 31-DEC-05 1 1 01-SEP-05 31-OCT-05 1 1 01-JUN-05 31-JUL-05 1 2
Using this inline query, it turns out surprisingly simple to find the overlapping assignments:
select empno , projno , start_date , end_date , next_stint from ( select lead(start_date,1) over (partition by empno order by start_date) next_stint , start_date , end_date , projno , empno from project_assignments ) where next_stint < end_date / EMPNO PROJNO START_DAT END_DATE NEXT_STIN ---------- ---------- --------- --------- --------- 1 1 01-AUG-05 31-DEC-05 01-SEP-05
We find that EMPNO 1 has an assignment that starts on August 1st 2005 that has an overlap with the next assignment for this employee, starting on the 1st of September.
Having come this far, it is easy to see how we can go one step further, and implement the business rule that Assignments may not overlap in a declarative manner, using the Materialized View trick again.
First we have to create a Materialized View Log and the Materialized View itself:
create materialized view log on project_assignments with rowid(projno,start_date,end_date) including new values / create materialized view project_assignments_mv refresh on commit as select lead(start_date,1) over (partition by empno order by start_date) next_stint , start_date , end_date , projno , empno from project_assignments /
Then we want to specify that there may not be any project_assignments where next_stint starts before the end_date, in a declarative manner. We can do this by defining a check constraint on the Materialized View:
alter table project_assignments_mv add constraint pat_mv_chk1 check (next_stint > end_date) deferrable; ERROR at line 2: ORA-02293: cannot validate (SCOTT.PAT_MV_CHK1) - check constraint violated
In our case, this fails, since we have an offencing record in the Materialized View. Let’s fix that problem and see whether we can create the constraint after all:
update project_assignments set start_date = '01-nov-2005' where start_date='01-AUG-2005' / 1 row updated. commit; alter table project_assignments_mv add constraint pat_mv_chk1 check (next_stint > end_date) deferrable; Table altered.
Notice the Commit. It is required, since the update on project_assignments will only be applied to the Materialized View at commit time.
So now the constraint is in place, we can see whether is does its job. Will we be able to create a new project assignment that overlaps with an existing one?
insert into project_assignments values (1,2,'15-aug-2005','29-sep-2005') / 1 row created. SQL> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-02290: check constraint (SCOTT.PAT_MV_CHK1) violated
Clearly not! Again, only when we commit will the MV be refreshed and the Constraint be enforced.
- Introducing Materialized Views as mechanism for Business Rule implementation (complex declarative constraints)
- Analytical Power
- Introduction to Analytical Functions – Use of Analytics as alternative for Simple Aggregations
- Analytical SQL Functions – theory and examples – Part 1 on the Partition Clause
- Analytical SQL Functions – theory and examples – Part 2 on the Order By and Windowing Clauses