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.