fareed fareed - 3 months ago 9
SQL Question

Select Common Dates in Date Ranges

I have a select statement that returns date ranges (From and To columns) and I want to generate a list of all dates that are common within all the ranges.

For example:

FROM TO
01-12-2016 11-12-2016
05-12-2016 17-12-2016
07-12-2016 20-12-2016


Now I want to get all the dates that have occurred in all the ranges above. In this case the following dates should be retrieved.

07-12-2016
08-12-2016
09-12-2016
10-12-2016
11-12-2016


because these dates are common in all the returned results. Is this possible with SQL (or PL/SQL)?

Answer

Sample data:

CREATE TABLE tt1 (
  "FROM" date, "TO" date
);

INSERT INTO tt1 VALUES( date '2016-12-01', date '2016-12-11' );
INSERT INTO tt1 VALUES( date '2016-12-05', date '2016-12-17' );
INSERT INTO tt1 VALUES( date '2016-12-07', date '2016-12-20' );
COMMIT;

The query:

SELECT d1 + level - 1 As da_te
FROM (
      SELECT max( "FROM" ) as d1, 
             min( "TO" ) as d2
      FROM tt1
)
CONNECT BY LEVEL <= d2 - d1 + 1

Results:

DA_TE    
----------
2016/12/07 
2016/12/08 
2016/12/09 
2016/12/10 
2016/12/11