• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

reject overlapping date range within insert

I have a table

create table test_table(id number,start_date date not null,end_date date);

insert into  test_table values (1,sysdate, null);

Now if I want to insert another new row, I need a check condition, such that new row with the same id can't have overlapping date range.

Before inserting a new row the enddate will be updated and the new record start date must be greater than enddate of the exiting data.

3 Solutions
Walter RitzelSenior Software EngineerCommented:
I would use the following algorithm:
- insert the new record in a staging table;
- make a select minus between staging table and final table - all records from staging, but only the records with end date = null from the final table;
- on the select minus, all columns with the exception of the date fields should be compared;
- for each record resulting from the select minus, do the following:
     - update on final table the enddate to sysdate - 1 where the natural key matches and the enddate is null;
     - insert a new record on the final table, with start date equal to sysdate and end date equal to null.

Hope this helps.
vishali_vishuAuthor Commented:
I know a way with triggers

1. Row Level trigger (Before Insert): record the new rows into package variables (collections)

2. Statement Level trigger(Before Insert): Read the package variable and check the condition and if condition not satisfied raise exception.
HainKurtSr. System AnalystCommented:
create a sp

ins_rec(id, sd, ed)

here check the table for existing overlapping values, if there is one, adjust the dates or raise an exception and use this sp to insert records...

If you create an SP it should be fairly simple.

Do let us know if you need our help in SP logic
insert into  test_table (id,start_date)
select l_id , l_start_date from dual where start_date > (select max(end_date) from test_table)

above insert will insert values stored in variables l_id and l_start_date only if start_date is greater than max. end_date.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now