CODEWITHSUNDEEP
sqlsql-serverdatetime
Alexander
Alexander

Reputation: 189

How to determine consecutive date records with overlaps in SQL Server

I know there are similar questions, but didn't find the answer for this specific case. Assume I have the following date ranges in the table (order by dates) :

 StartDate   -   EndDate      DoW
----------------------------------
 2017-01-10  -   2017-01-15    1
 2017-01-16  -   2017-01-19    2
 2017-01-17  -   2017-01-19    3
 2017-01-18  -   2017-01-21    4
 2017-01-22  -   2017-01-28    5

The expected result is TRUE. This set contains consecutive date records (no gaps between) with overlaps

 StartDate   -   EndDate      DoW
----------------------------------
 2017-01-10  -   2017-01-15    1
 2017-01-16  -   2017-01-19    2
 2017-01-20  -   2017-01-23    3
 2017-01-24  -   2017-01-26    4
 2017-01-27  -   2017-01-28    5

The expected result is TRUE. This set contains consecutive date records (no overlaps)

And for this case :

StartDate   -   EndDate      DoW
---------------------------------
 2017-01-10  -   2017-01-15    1
 2017-01-17  -   2017-01-19    2
 2017-01-17  -   2017-01-19    3
 2017-01-18  -   2017-01-21    4
 2017-01-22  -   2017-01-28    5

The result should be FALSE because there is a gap between 1st and 2nd rows (2017-01-16 is missing).

Thanks in advance.

Upvotes: 1

Views: 214

Answers (4)

Jovana
Jovana

Reputation: 380

You could try just joining ranges by their order and comparing the last day of the first range and the first day of the second range

SELECT  iif(count(*)>0, 0, 1) as result 
FROM    t prev 
left outer join t next on (prev.Dow+1) = next.Dow 
where
DATEADD(d, 1, prev.EndDate) < next.StartDate

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use lead to get this as below:

select case when count(*) = sum(nextdt) then 'true' else 'false' end as Result
from (
    select *, NextDt = case when dateadd(dd, 1, enddate) = lead(startdate, 1, dateadd(dd, 1, enddate)) over(order by startdate) then 1 else 0 end from #yourconsequentdate
) a

Upvotes: 0

etsa
etsa

Reputation: 5060

If the order is as in your sample data, you could try this, which uses LAG() function:

CREATE TABLE TT1 (STARTDATE DATE, ENDDATE DATE, DOW INT);

1)

INSERT INTO TT1 VALUES ('2017-01-10','2017-01-15',    1);
 INSERT INTO TT1 VALUES ('2017-01-16','2017-01-19',    2);
 INSERT INTO TT1 VALUES ('2017-01-17','2017-01-19',    3);
 INSERT INTO TT1 VALUES ('2017-01-18','2017-01-21',    4);
 INSERT INTO TT1 VALUES ('2017-01-22','2017-01-28',    5);


SELECT MIN(CHCK)  AS CHK
FROM (
        SELECT CASE WHEN DATEDIFF(dd, LAG(ENDDATE) OVER (ORDER BY DOW), STARTDATE) >1 THEN 0 ELSE 1 END AS CHCK
        FROM TT1
       ) A;

 DELETE FROM TT1;

Output 1:

+-----+
| CHK |
+-----+
| 1   |
+-----+

2)

 INSERT INTO TT1 VALUES ('2017-01-10','2017-01-15',    1);
 INSERT INTO TT1 VALUES ('2017-01-16','2017-01-19',    2);
 INSERT INTO TT1 VALUES ('2017-01-20','2017-01-23',    3);
 INSERT INTO TT1 VALUES ('2017-01-24','2017-01-26',    4);
 INSERT INTO TT1 VALUES ('2017-01-27','2017-01-28',    5);

SELECT MIN(CHCK)  AS CHK
FROM (
        SELECT CASE WHEN DATEDIFF(dd, LAG(ENDDATE) OVER (ORDER BY DOW), STARTDATE) >1 THEN 0 ELSE 1 END AS CHCK
        FROM TT1
       ) A;

 DELETE FROM TT1;

Output 2:

+-----+
| CHK |
+-----+
| 1   |
+-----+

3)

 INSERT INTO TT1 VALUES ('2017-01-10','2017-01-15',    1);
 INSERT INTO TT1 VALUES ('2017-01-17','2017-01-19',    2);
 INSERT INTO TT1 VALUES ('2017-01-17','2017-01-19',    3);
 INSERT INTO TT1 VALUES ('2017-01-18','2017-01-21',    4);
 INSERT INTO TT1 VALUES ('2017-01-22','2017-01-28',    5);

SELECT MIN(CHCK)  AS CHK
FROM (
        SELECT CASE WHEN DATEDIFF(dd, LAG(ENDDATE) OVER (ORDER BY DOW), STARTDATE) >1 THEN 0 ELSE 1 END AS CHCK
        FROM TT1
       ) A;

Output 3:

+-----+
| CHK |
+-----+
| 0   |
+-----+

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

If there is a gap, it is going to be one day before the end date or one day after the start date. You can get this list of dates and check for gaps. It is tricky, because you don't want the first and last of these dates . . . but, you can just set a threshhold of 2:

with d as (
      select dateadd(day, -1, startdate) as dte
      from t
      union all  -- do not remove duplicates!
      select dateadd(day, + 1, enddate) as dte
      from t
     )
select (case when count(*) > 2 then 'false' else 'true' end)
from d
where not exists (select 1
                  from t
                  where d.dte >= t.startdate and
                        d.dte <= t.enddate
                 );

Upvotes: 3

Related Questions