Suppose I have a table which stores details of exclusive licenses for a product, so that the table contains a START_DATE and END_DATE for a license.
I want to write a function which, if fed a
proposed start/end date for a
new license will check to see if any part of the proposed date range overlaps with any part of the existing license.
I have thought to code something like this (pseudocode, not 100% tested!):
create function checkdates (p_productid number, p_instart date, p_inend date)
return number
is
v_start date;
v_end date;
v_return number;
begin
select start_date, end_date into v_start, v_end from tbl_licenses where product_id=p_productid;
if p_instart between v_start and v_end then
--the start date is between the existing licenses' start and end dates, so there is an overlap
v_return := 0;
end if;
if p_instart not between v_start and v_end AND p_inend between v_start and v_end
--the start date does not overlap the existing license, but the proposed ending does
v_return := 0;
end if;
if p_instart not between v_start and v_end AND p_inend not between v_start and v_end
--neither the proposed start or end dates overlap the existing dates, so it must be OK
v_return := 1;
end if;
return v_return;
end;
Suppose the existing license has a start date of January 1st 2010 and an end date of 31st December 2015. Now suppose that, for that same product ID, someone is proposing a license to run from 1st September 2009 to 31st August 2010. The proposed start is not between the existing license dates, so that first check is ignored. But the proposed end date
is between the existing dates, and so the second test must kick in and cause a 0 to be returned. These three tests therefore seem to me to be logical and complete.
Two questions arise, however: is that logic really 100% sound? And two, is there a more efficient way of performing a check for overlapping date ranges in Oracle 10g Release 2?
Any thoughts gratefully received.