Sql query to find the dates between two

Question

I have a problem that I would like to get the dates which are logged between two dates (i.e start& end dates).

I have a table with the following records in it:

Create table t ( a int, b date, c date );

insert into t values(1, to_date( '01-jan-2015'), to_date( '15-jan-2015'));
insert into t values(2, to_date( '03-jan-2015'), to_date( '10-jan-2015'));
insert into t values(3, to_date( '12-jan-2015'), to_date( '25-jan-2015'));
insert into t values(4, to_date( '20-jan-2015'), to_date( '01-feb-2015'));
insert into t values(5, to_date( '05-feb-2015'), to_date( '10-feb-2015'));

Is there any way using a SELECT statement to determine the dates that exist within the date range given in this table?

EX: the value between '01-jan-2015' to_date '15-jan-2015' is already there. Anyways there is again two values logged on '03-jan-2015' and '12-jan-2015'. (whereas those were alreday defined between('01-jan-2015' to_date '15-jan-2015')) So, I need to get the output as "values for start_date "'03-jan-2015' is already logged" without giving the date range in the query. that is, it should automatically pull the record for given value.

This assumes that when date ranges overlap, it will eliminate a overload of values between the date range

Hope this makes sense.Kindly help me on to solve this.

Thanks, Shruthi

Answer 1

Try the below query, that will list out all the overlapping entries.

SELECT  *
FROM    t as t1
WHERE EXISTS (
        SELECT  1
        FROM    t as t2
        WHERE   t1.a != t2.a
            AND t1.b <= t2.c AND t1.c >= t2.b
    )

Or try this with a left join

SELECT  t1.*,t2.a as overlap_id
FROM    t as t1
LEFT JOIN t as t2   ON t1.a > t2.a
            AND t1.b <= t2.c AND t1.c >= t2.b
WHERE   t2.a IS NOT NULL

a   b           c           overlap_id
--------------------------------------
2   2015-01-03  2015-01-10  1         --2 overlapped with 1
3   2015-01-12  2015-01-25  1         --3 overlapped with 1
4   2015-01-20  2015-02-01  3         --4 overlapped with 3