1 2 3 Previous Next 35 Replies Latest reply on Dec 19, 2019 4:00 PM by Solomon Yakobson

# Check the consecutive dates(overlapping rows)

hello to all and thanks for help in advance

Below are my details:

```--create table.
create table t02(
fname varchar2(10),
city varchar2(10),
country varchar2(5),
start_date date,
end_date date);
/
insert into t02 values('Tom','Boston','US','01-JAN-2001', '31-JUL-2001');
insert into t02 values('Tom','Nashua','US','01-AUG-2001', '31-DEC-2001');
insert into t02 values('Tom','New York','US','01-JAN-2002', '31-DEC-2002');
insert into t02 values('Tom','Mumbai','IND','01-JAN-2003', '31-DEC-2003');
insert into t02 values('Tom','Dallas','US','01-JAN-2004', '31-DEC-2004');
insert into t02 values('Tom','Austin','US','01-JUL-2004', '31-DEC-2006');  -- How can I check it? IT would not possible insert a row where date is inside others, each start_date have to be > of previous end date of prev row
/
commit;

/
select * from t02
order by start_date

```

check must be in a BEFORE trigger

Thanks

• ###### 1. Re: Check the consecutive dates(overlapping rows)

OK thanks I found

• ###### 2. Re: Check the consecutive dates(overlapping rows)

There are several levels of complexity for constraints.

The simplest constraints involve a single value at a time. A "value" is found in a single column of a single row. The NOT NULL constraint is perhaps the simplest example of such a constraint. Such constraints can be defined "inline" (right where you define the column in CREATE TABLE) or out-of-line (after all columns have been defined).

More complex constraints reference values from at least two different columns, but all in the same row. These can only be written out-of-line.

Even more complex constraints reference values from other ROWS in the same table. One example is the UNIQUE constraint - and, luckily, at least THAT type of constraint is handled for us by the database itself. (Similarly, referential integrity constraints may involve values from different TABLES, let alone from different ROWS in the same table; but they are handled for us by the database.)

Alas, cross-row constraints - even in the same table - are notoriously difficult to implement, with the exception of UNIQUE which the database handles. There is no way to do it just with a BEFORE trigger, if you mean row-level trigger. (It can be done with a statement-level trigger, but that has serious performance consequences.)

As far as I know, there are two main ways to enforce a constraint like what you are requesting. One is a compound trigger, the other is a materialized view approach. Both are quite a bit more advanced than a simple row-level trigger.

So - which way do you plan to go? Or did you really mean a statement-level BEFORE trigger?

• ###### 3. Re: Check the consecutive dates(overlapping rows)

Rosario Vigilante wrote:

OK thanks I found

What you found is fine and dandy - but you can't use that check in a TRIGGER - in a (row-level) trigger you can't select from the same table you are trying to modify. You will get the friendly "mutating table" error.

• ###### 4. Re: Check the consecutive dates(overlapping rows)

That's reactive approach. You can do more proactive approach using deferrable FK:

create table t02(

fname varchar2(10),

city varchar2(10),

country varchar2(5),

start_date date,

end_date date

)

/

alter table t02

unique(start_date)

/

alter table t02

unique(end_date)

/

alter table t02

foreign key(start_date)

references t02(end_date) deferrable initially deferred

/

SQL> insert into t02 values('Tom','Boston','US','01-JAN-2001', '31-JUL-2001')

2  /

1 row created.

SQL> insert into t02 values('Tom','New York','US','01-JAN-2002', '31-DEC-2002')

2  /

1 row created.

SQL> insert into t02 values('Tom','Mumbai','IND','01-JAN-2003', '31-DEC-2003')

2  /

1 row created.

SQL> insert into t02 values('Tom','Dallas','US','01-JAN-2004', '31-DEC-2004')

2  /

1 row created.

SQL> insert into t02 values('Tom','Austin','US','01-JUL-2004', '31-DEC-2006')

2  /

1 row created.

SQL> commit

2  /

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

SQL>

SY.

• ###### 5. Re: Check the consecutive dates(overlapping rows)

Really nice approach!

• ###### 6. Re: Check the consecutive dates(overlapping rows)

Actually you need to clarify "Check the consecutive dates(overlapping rows)". Based on sample provided you need to check for overlapping but not for consecutive dates. Solution I provided ensures dates are consecutive, therefore no gaps and no overlaps are allowed (at commit point).

SY.

• ###### 7. Re: Check the consecutive dates(overlapping rows)

It looks like there has to be a starting point with a NULL start date. In fact, why can't there be more than one?

```SQL> truncate table T02;

Table T02 truncated.

SQL> insert into t02(start_date,end_date) values(null, date '2000-01-01');

1 row inserted.

SQL> commit;

Commit complete.

SQL> insert into T02(start_date,end_date) values(null, date '2000-02-02');

1 row inserted.

SQL> commit;

Commit complete.

SQL> insert into T02(start_date,end_date) values(date '2000-01-01', date '2000-03-03');

1 row inserted.

SQL> commit;

Commit complete.

SQL> insert into T02(start_date,end_date) values(date '2000-02-02', date '2000-04-04');

1 row inserted.

SQL> commit;

Commit complete.
```

Don't we need an additional constraint that makes the NULL start date unique?

Also I suppose you are assuming the OP will think of the CHECK constraint that makes END_DATE later than START_DATE?

```QL> truncate table T02;

Table T02 truncated.

SQL> insert into t02(start_date,end_date) values(null, date '2000-01-01');

1 row inserted.

SQL> insert into t02(start_date,end_date) values(date '2000-01-01', date '2001-01-01');

1 row inserted.

SQL> insert into t02(start_date,end_date) values(date '2001-01-01', date '2000-06-01');

1 row inserted.

SQL> commit;

Commit complete.
```

Best regards,

Stew Ashton

• ###### 8. Re: Check the consecutive dates(overlapping rows)

create unique index to2_uidx1

on t02(

nvl(start_date,date '-4712-12-31')

)

/

SY.

• ###### 9. Re: Check the consecutive dates(overlapping rows)

Stew Ashton wrote:

It looks like there has to be a starting point with a NULL start date.

Not necessarily. We do need starting point. It can be either null start date or row with start date = end date. For a more generic solution without a need for null start date or a start row we can use fast refresh on commit materialized view.

SY.

• ###### 10. Re: Check the consecutive dates(overlapping rows)

Trigger + end map table solution:

create table t02(

fname varchar2(10),

city varchar2(10),

country varchar2(5),

start_date date not null,

end_date date not null

)

/

alter table t02

check(end_date >= start_date)

/

create table t02_end_map

as

select  cast(null as date) first_start_date,

cast(null as date) last_end_date

from  dual

/

create or replace

trigger t02_biudr

before insert

or update

or delete

on t02

for each row

declare

v_first_start_date date;

v_last_end_date   date;

begin

select  first_start_date,

last_end_date

into  v_first_start_date,

v_last_end_date

from  t02_end_map

for update;

if inserting

then

if :new.start_date = nvl(v_last_end_date + 1,:new.start_date)

then

update t02_end_map

set first_start_date = nvl(first_start_date,:new.start_date),

last_end_date    = :new.end_date;

elsif :new.end_date = nvl(v_first_start_date - 1,:new.end_date)

then

update t02_end_map

set first_start_date = :new.start_date,

last_end_date    = nvl(last_end_date,:new.end_date);

else

raise_application_error(

-20500,

'Gap/overlap detected.'

);

end if;

elsif updating

then

if :old.end_date = v_last_end_date and :old.start_date = :new.start_date

then

update t02_end_map

set last_end_date    = :new.end_date;

elsif :old.start_date = v_first_start_date and :old.end_date = :new.end_date

then

update t02_end_map

set first_start_date = :new.start_date;

elsif :old.end_date = v_last_end_date and :new.end_date = v_first_start_date - 1

then

update t02_end_map

set first_start_date = :new.start_date,

last_end_date    = :old.start_date - 1;

elsif :old.start_date = v_first_start_date and :new.start_date = v_last_end_date + 1

then

update t02_end_map

set first_start_date = :old.end_date + 1,

last_end_date    = :new.end_date;

else

raise_application_error(

-20500,

'Gap/overlap detected.'

);

end if;

else

if :old.end_date = v_last_end_date

then

update t02_end_map

set last_end_date = case

when v_first_start_date <= :old.start_date - 1 then :old.start_date - 1

end;

elsif :old.start_date = v_first_start_date

then

update t02_end_map

set first_start_date = case

when v_last_end_date >= :old.end_date + 1 then :old.end_date + 1

end;

else

raise_application_error(

-20500,

'Gap/overlap detected.'

);

end if;

end if;

end;

/

Now:

SQL> insert into t02 values('Tom','Boston','US','01-JAN-2001', '31-JUL-2001')

2  /

1 row created.

SQL> insert into t02 values('Tom','New York','US','01-JAN-2002', '31-DEC-2002')

2  /

insert into t02 values('Tom','New York','US','01-JAN-2002', '31-DEC-2002')

*

ERROR at line 1:

ORA-20500: Gap/overlap detected.

ORA-06512: at "SCOTT.T02_BIUDR", line 22

ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

SQL> insert into t02 values('Tom','New York','US','01-AUG-2001', '31-DEC-2002')

2  /

1 row created.

SQL> insert into t02 values('Tom','Mumbai','IND','01-OCT-2001', '01-NOV-2002')

2  /

insert into t02 values('Tom','Mumbai','IND','01-OCT-2001', '01-NOV-2002')

*

ERROR at line 1:

ORA-20500: Gap/overlap detected.

ORA-06512: at "SCOTT.T02_BIUDR", line 22

ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

SQL> insert into t02 values('Tom','Mumbai','IND','01-OCT-2000', '01-NOV-2000')

2  /

insert into t02 values('Tom','Mumbai','IND','01-OCT-2000', '01-NOV-2000')

*

ERROR at line 1:

ORA-20500: Gap/overlap detected.

ORA-06512: at "SCOTT.T02_BIUDR", line 22

ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

SQL> insert into t02 values('Tom','Mumbai','IND','01-OCT-2000', '31-DEC-2000')

2  /

1 row created.

SQL> insert into t02 values('Tom','Dallas','US','01-jan-03','31-dec-03')

2  /

1 row created.

SQL> select * from t02 order by start_date;

FNAME      CITY       COUNT START_DAT END_DATE

---------- ---------- ----- --------- ---------

Tom        Mumbai     IND   01-OCT-00 31-DEC-00

Tom        Boston     US    01-JAN-01 31-JUL-01

Tom        New York   US    01-AUG-01 31-DEC-02

SQL> update t02

2     set end_date = date '2002-12-01'

3   where city = 'New York'

4  /

1 row updated.

SQL> select * from t02 order by start_date;

FNAME      CITY       COUNT START_DAT END_DATE

---------- ---------- ----- --------- ---------

Tom        Mumbai     IND   01-OCT-00 31-DEC-00

Tom        Boston     US    01-JAN-01 31-JUL-01

Tom        New York   US    01-AUG-01 01-DEC-02

SQL> update t02

2     set end_date = date '2003-12-01'

3   where city = 'New York'

4  /

1 row updated.

SQL> select * from t02 order by start_date;

FNAME      CITY       COUNT START_DAT END_DATE

---------- ---------- ----- --------- ---------

Tom        Mumbai     IND   01-OCT-00 31-DEC-00

Tom        Boston     US    01-JAN-01 31-JUL-01

Tom        New York   US    01-AUG-01 01-DEC-03

SQL> select * from t02 order by start_date;

FNAME      CITY       COUNT START_DAT END_DATE

---------- ---------- ----- --------- ---------

Tom        Mumbai     IND   01-OCT-00 31-DEC-00

Tom        Boston     US    01-JAN-01 31-JUL-01

Tom        New York   US    01-AUG-01 01-DEC-02

SQL> update t02

2     set end_date = date '2003-12-01'

3   where city = 'New York'

4  /

1 row updated.

SQL> select * from t02 order by start_date;

FNAME      CITY       COUNT START_DAT END_DATE

---------- ---------- ----- --------- ---------

Tom        Mumbai     IND   01-OCT-00 31-DEC-00

Tom        Boston     US    01-JAN-01 31-JUL-01

Tom        New York   US    01-AUG-01 01-DEC-03

SQL> update t02

2     set start_date = date '2000-11-01'

3  /

update t02

*

ERROR at line 1:

ORA-20500: Gap/overlap detected.

ORA-06512: at "SCOTT.T02_BIUDR", line 40

ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

SQL> update t02

2     set start_date = date '2000-07-01'

3  /

update t02

*

ERROR at line 1:

ORA-20500: Gap/overlap detected.

ORA-06512: at "SCOTT.T02_BIUDR", line 40

ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

SQL> update t02

2     set start_date = date '2000-11-01'

3   where city = 'Mumbai'

4  /

1 row updated.

SQL> select * from t02 order by start_date;

FNAME      CITY       COUNT START_DAT END_DATE

---------- ---------- ----- --------- ---------

Tom        Mumbai     IND   01-NOV-00 31-DEC-00

Tom        Boston     US    01-JAN-01 31-JUL-01

Tom        New York   US    01-AUG-01 01-DEC-03

SQL> update t02

2     set start_date = date '2000-07-01'

3   where city = 'Mumbai'

4  /

1 row updated.

SQL> update t02

2     set start_date = date '2001-01-05'

3   where city = 'Boston'

4  /

update t02

*

ERROR at line 1:

ORA-20500: Gap/overlap detected.

ORA-06512: at "SCOTT.T02_BIUDR", line 40

ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

SQL> update t02

2     set start_date = date '2000-11-01'

3   where city = 'Boston'

4  /

update t02

*

ERROR at line 1:

ORA-20500: Gap/overlap detected.

ORA-06512: at "SCOTT.T02_BIUDR", line 40

ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

SQL> update t02

2     set end_date = date '2001-03-01'

3   where city = 'Boston'

4  /

update t02

*

ERROR at line 1:

ORA-20500: Gap/overlap detected.

ORA-06512: at "SCOTT.T02_BIUDR", line 40

ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

SQL> update t02

2     set end_date = date '2002-03-01'

3   where city = 'Boston'

4  /

update t02

*

ERROR at line 1:

ORA-20500: Gap/overlap detected.

ORA-06512: at "SCOTT.T02_BIUDR", line 40

ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

SQL> delete t02

2   where city = 'Boston'

3  /

delete t02

*

ERROR at line 1:

ORA-20500: Gap/overlap detected.

ORA-06512: at "SCOTT.T02_BIUDR", line 59

ORA-04088: error during execution of trigger 'SCOTT.T02_BIUDR'

SQL> delete t02

2   where city = 'New York'

3  /

1 row deleted.

SQL> delete t02

2   where city = 'Boston'

3  /

1 row deleted.

SQL> delete t02

2   where city = 'Mumbai'

3  /

1 row deleted.

SQL> select * from t02_end_map;

FIRST_STA LAST_END_

--------- ---------

SQL>

This is pessimistic solution - it serializes the process comparing to optimistic FAST REFRESH ON COMMIT or deferred FK solution which assumes transactions make valid inserts.

SY.

P.S. Corrected IF UPDATING.

• ###### 11. Re: Check the consecutive dates(overlapping rows)

Solomon Yakobson wrote:

Stew Ashton wrote:

It looks like there has to be a starting point with a NULL start date.

Not necessarily. We do need starting point. It can be either null start date or row with start date = end date. For a more generic solution without a need for null start date or a start row we can use fast refresh on commit materialized view.

SY.

You missed my point. To avoid overlaps, we need a UNIQUE starting point. With the solution you first proposed - don't change the goal posts yet - there can be multiple starting points with null start dates or multiple starting points with start data = end date.

[UPDATE 2019-12-15: come to think of it, a row with start date = end date cannot be a "starting point"! Suppose we insert a row with start_date = end_date = 2000-01-01. There are unique constraints on start_date and end_date; that means 2000-01-01 cannot be used again in any row, either in start_date or end_date. With Solomon's solution, a real "starting point" has to have a NULL start date. With my solution below, the "starting point" has to have a NULL end date.]

Building on your first solution, I would be tempted to do this:

• Make the end date an exclusive end date as you do. The OP uses inclusive end dates.
• The start date cannot be null, but the end date can be null to indicate the indefinite future. This way we don't need a fake row to start things off.
• Check constraint: start date must be less than end date.
• Unique constraints on start data and end date.
• A deferred constraint where the end date references some other start date (like your constraint but going the other way).
• A virtual column that is 'Y' when the end date is null, plus a unique constraint on that column.

```create table t02(
fname varchar2(10),
city varchar2(10),
country varchar2(5),
start_date date not null
constraint t02_start_date_uk unique,
end_date date
constraint t02_end_date_uk unique,
is_ending_point invisible
generated always as (case when end_date is null then 'Y' end)
constraint t02_one_ending_point_only unique,
constraint t02_start_lt_end_ck check (start_date < end_date),
constraint t02_end_date_eq_other_start
foreign key(end_date)
references t02(start_date) deferrable initially deferred
);
```

Another way of getting the same result with much less trouble: just get rid of the end date!

```create table t02(
fname varchar2(10),
city varchar2(10),
country varchar2(5),
effective_date date not null
constraint t02_effective_date_uk unique
);

create or replace view v_t02 as
select fname, city, country,
effective_date start_date,
(select min(effective_date)
from t02
where effective_date > a.effective_date) end_date
from t02 a;
```

Either way there must be a known first start date, there can be no gaps or overlaps or known final end date.

Regards,

Stew

• ###### 12. Re: Check the consecutive dates(overlapping rows)

select * from (

select     fname,city,country,start_date,

lag(start_date,1) over (order by start_date) pre_start,

lag(end_date,1) over (order by end_date) pre_end  from t02)

where start_date between pre_start and pre_end;

output

 FNAME CITY COUNTRY START_DATE PRE_START PRE_END Tom Austin US 01-07-2004 01-01-2004 31-12-2004
• ###### 13. Re: Check the consecutive dates(overlapping rows)

Stew Ashton wrote:

Building on your first solution, I would be tempted to do this:

• Make the end date an exclusive end date as you do. The OP uses inclusive end dates.
• The start date cannot be null, but the end date can be null to indicate the indefinite future. This way we don't need a fake row to start things off.
• Check constraint: start date must be less than end date.
• Unique constraints on start data and end date.
• A deferred constraint where the end date references some other start date (like your constraint but going the other way).
• A virtual column that is 'Y' when the end date is null, plus a unique constraint on that column.

Inclusive dates can be handled by a virtual column. Not sure why start date can't be null to indicate indefinite past. Check constraint is, indeed, needed (you can see it in my trigger solution) but it should be start date must be less or equal than end date. In regards to "This way we don't need a fake row to start things off". That will work for indefinite future intervals only. How would you insert "Boston", for example:

SQL> create table t22(

2    fname varchar2(10),

3    city varchar2(10),

4    country varchar2(5),

5    start_date date not null

6      constraint t22_start_date_uk unique,

7    end_date date

8      constraint t22_end_date_uk unique,

9    is_ending_point invisible

10      generated always as (case when end_date is null then 'Y' end)

11      constraint t22_one_ending_point_only unique,

12    constraint t22_start_lt_end_ck check (start_date < end_date),

13    constraint t22_end_date_eq_other_start

14      foreign key(end_date)

15      references t22(start_date) deferrable initially deferred

16  )

17  /

Table created.

SQL> insert into t22 values('Tom','Boston','US',date '2001-01-01',date '2001-07-31')

2  /

1 row created.

SQL> commit

2  /

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

SQL> insert into t22 values('Tom','Boston','US',date '2001-01-01',null)

2  /

1 row created.

SQL> update t22 set end_date = date '2001-07-31' where city = 'Boston'

2  /

1 row updated.

SQL> commit

2  /

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

SQL>

You would have to keep fake indefinite end date row:

SQL> insert into t22 values('Tom','Boston','US',date '2001-01-01',date '2001-07-31')

2  /

1 row created.

SQL> insert into t22 values('Tom','Boston','US',date '2001-07-31',null)

2  /

1 row created.

SQL> commit

2  /

Commit complete.

SQL>

SY.

• ###### 14. Re: Check the consecutive dates(overlapping rows)

Solomon Yakobson wrote:

...

Inclusive dates can be handled by a virtual column.

True. I was merely pointing out that in your first reply you used exclusive dates (which I recommend).

Not sure why start date can't be null to indicate indefinite past.

It can, but I chose to switch to null end date to indicate indefinite future. Can we have both indefinite? I suppose, but at the cost of more constraints.

Check constraint is, indeed, needed (you can see it in my trigger solution) but it should be start date must be less or equal than end date.

I disagree.

In regards to "This way we don't need a fake row to start things off". That will work for indefinite future intervals only.

Correct. As I stated, "Either way there must be a known first start date, there can be no gaps or overlaps or known final end date."

Regards,

Stew