CODEWITHSUNDEEP
sqloracle-databasealgorithmplsql
Nickon
Nickon

Reputation: 10146

Dates range overlapping with infinite intervals

I have a function in Oracle that checks if two dates overlap each other. There is no problem with it, because [a, b] overlaps with [x, y] if b > x and a < y. Works fine for defined start/end date 1 and start/end date 2. But now I want to modify it. If any of given dates is NULL it should be treated as +-infinite.

It's a code:

  FUNCTION OVERLAP(p_START_DATE_1 DATE, p_END_DATE_1 DATE,
   p_START_DATE_2 DATE, p_END_DATE_2 DATE) RETURN VARCHAR2 AS

   lv_RESULT VARCHAR2(1);
  BEGIN 
       lv_RESULT := 'N';      

       IF (p_START_DATE_1 <= p_END_DATE_2 AND p_END_DATE_1 >= p_START_DATE_2) THEN
         lv_RESULT := 'Y';
       END IF;

       RETURN lv_RESULT;
  END OVERLAP;

For instance: Suppose that p_START_DATE_1 is NULL. In this case that code:

SELECT MY_PACKAGE_SQL.OVERLAP(
       NULL,
       TO_DATE('01/12/2014', 'DD/MM/YYYY'),
       TO_DATE('01/02/2012', 'DD/MM/YYYY'),
       TO_DATE('01/05/2012', 'DD/MM/YYYY'))
FROM DUAL;

... should return Y, because first date range is (-infinite, 01/12/2014] and it overlaps with [01/02/2012, 01/05/2012].

Now my problem... I know I can use additional "IFs" to check NULLs. But I wonder if it's any other solution to make it faster in Oracle's PL\SQL language? It's like do it in smarter way challenge in my team :)

Upvotes: 0

Views: 314

Answers (2)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 89

if nvl(p_START_DATE_1<=p_END_DATE_2 and p_START_DATE_2<=p_END_DATE_1, true) then

EDIT :
To check if [start1, end1] contains [start2, end2]: 

if nvl(start1 <= nvl(start2,start1 - 1) and end1 >= nvl(end2,end1 + 1),true) then

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Here is one method to implement this:

   IF (coalesce(p_START_DATE_1, p_end_date_2, sysdate) <= coalesce(p_END_DATE_2, p_START_DATE_1, sysdate) AND
       coalesce(p_END_DATE_1, p_START_DATE_2, sysdate) >= coalesce(p_START_DATE_2, p_END_DATE_1, sysdate) THEN

The idea is to replace the NULL values with values that will satisfy each condition. If both are NULL, then use sysdate.

Upvotes: 2

Related Questions