Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ORACLE SQL Date range intersections

I have a table T1, it contains a NAME value (not unique), and a date range (D1 and D2 which are dates) When NAME are the same, we make a union of the date ranges (e.g. B).

But as a result (X), we need to make intersection of all the date ranges

Edit: Table T1

NAME | D1       | D2
A    | 20100101 | 20101211
B    | 20100120 | 20100415
B    | 20100510 | 20101230
C    | 20100313 | 20100610

Result :

X    | 20100313 | 20100415
X    | 20100510 | 20100610

Visually, this will give the following :

NAME        : date range
A           : [-----------------------]-----
B           : --[----]----------------------
B           : ----------[---------------]---
C           : -----[--------]---------------

Result :

X           : -----[-]----------------------
X           : ----------[---]---------------

Any idea how to get that using SQL / PL SQL ?

like image 489
guigui42 Avatar asked Aug 13 '10 12:08

guigui42


People also ask

How do you know if two date ranges overlap in SQL?

You can do this by swapping the ranges if necessary up front. Then, you can detect overlap if the second range start is: less than or equal to the first range end (if ranges are inclusive, containing both the start and end times); or. less than (if ranges are inclusive of start and exclusive of end).

What is the range of dates valid in Oracle SQL?

Generally it's a good idea to post your 4-digit Oracle version and o/s information. Have a look at the Oracle Built-in Data Types in the documentation. You will find there that the "Valid date range from January 1, 4712 BC, to December 31, 9999 AD." in Oracle.

How do you get rid of OverLapping dates in SQL?

let's pick the big date SELECT ID, EMP_ID, [START DATE], MAX(END DATE) FROM (SELECT ID, EMP_ID, TEAM, [END DATE], MIN([START DATE]) [START DATE] FROM my_table GROUP BY ID, EMP_ID, END_DATE ) a GROUP BY ID, EMP_ID, [START DATE] -- Now we are done with similar end date and similar start date -- At this point I will write ...

Can you compare dates in Oracle?

You can use "classic comparators" (like >, >=, <, =...) to compare 2 dates. If you have date data types : don't convert them. if you have no choice, use something like: if to_char(d1,'YYYYMMDD') > to_char(d2,'YYYYMMDD') then ... else ...


1 Answers

here is a quick solution (may not be the most efficient):

SQL> CREATE TABLE myData AS
  2  SELECT 'A' name, date'2010-01-01' d1, date'2010-12-11' d2 FROM DUAL
  3  UNION ALL SELECT 'B', date'2010-01-20', date'2010-04-15' FROM DUAL
  4  UNION ALL SELECT 'B', date'2010-05-10', date'2010-12-30' FROM DUAL
  5  UNION ALL SELECT 'C', date'2010-03-13', date'2010-06-10' FROM DUAL;

Table created

SQL> WITH segments AS (
  2  SELECT dat seg_low, lead(dat) over(ORDER BY dat) seg_high
  3    FROM (SELECT d1 dat FROM myData
  4           UNION
  5           SELECT d2 dat FROM myData)
  6  )
  7  SELECT s.seg_low, s.seg_high
  8    FROM segments s
  9    JOIN myData m ON s.seg_high > m.d1
 10                 AND s.seg_low < m.d2
 11   GROUP BY s.seg_low, s.seg_high
 12  HAVING COUNT(DISTINCT NAME) = 3;

SEG_LOW     SEG_HIGH
----------- -----------
13/03/2010  15/04/2010
10/05/2010  10/06/2010

I build all the possible successive date ranges and join this "calendar" with the sample data. This will list all ranges that have 3 values. You may need to merge the result if you add rows:

SQL> insert into mydata values ('B',date'2010-04-15',date'2010-04-16');

1 row inserted

SQL> WITH segments AS (
  2  SELECT dat seg_low, lead(dat) over(ORDER BY dat) seg_high
  3    FROM (SELECT d1 dat FROM myData
  4           UNION
  5           SELECT d2 dat FROM myData)
  6  )
  7  SELECT MIN(seg_low), MAX(seg_high)
  8    FROM (SELECT seg_low, seg_high, SUM(gap) over(ORDER BY seg_low) grp
  9             FROM (SELECT s.seg_low, s.seg_high,
 10                           CASE
 11                              WHEN s.seg_low
 12                                   = lag(s.seg_high) over(ORDER BY s.seg_low)
 13                              THEN 0
 14                              ELSE 1
 15                           END gap
 16                      FROM segments s
 17                      JOIN myData m ON s.seg_high > m.d1
 18                                   AND s.seg_low < m.d2
 19                     GROUP BY s.seg_low, s.seg_high
 20                    HAVING COUNT(DISTINCT NAME) = 3))
 21   GROUP BY grp;

MIN(SEG_LOW) MAX(SEG_HIGH)
------------ -------------
13/03/2010   16/04/2010
10/05/2010   10/06/2010
like image 100
Vincent Malgrat Avatar answered Oct 10 '22 04:10

Vincent Malgrat