Select Common Dates in Date Ranges

Question

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.

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

krokodilko · Accepted Answer · 2016-08-15 07:22:15Z

1

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

answered Aug 15, 2016 at 7:22

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select Common Dates in Date Ranges

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related