Find gaps in date ranges - TSQL

Question

I have below SQL and I want gaps in below dates.

declare @startdate datetime = '2017-05-01'
declare @enddate datetime = '2017-05-25'

create table #tmpdates (id int, date1 datetime, date2 datetime, rate int)

insert into #tmpdates values (1, '2017-05-05', '2017-05-15', 10)
insert into #tmpdates values (2, '2017-05-16', '2017-05-18', 12)
insert into #tmpdates values (3, '2017-05-21', '2017-05-25', 15)

select * from #tmpdates where date1 >= @startdate and date2 <= @enddate

drop table #tmpdates

So output should contain 2017-05-01 to 2017-05-04 and 2017-05-19 to 2017-05-20 - 2 more records.

Output:
1   5/1/2017 0:00   5/4/2017 0:00   NO DATA
2   5/5/2017 0:00   5/15/2017 0:00  10
3   5/16/2017 0:00  5/18/2017 0:00  12
4   5/19/2017 0:00  5/20/2017 0:00  NO DATA
5   5/21/2017 0:00  5/25/2017 0:00  15

In my above query, only date range record returns.. Please guide or how can I include those too?

I'm really having trouble understanding what you want to achieve here. Can you try to explain it again? — Fredrik Lundin, Apr 06 '17 at 07:36
OP has a start date and an end date plus a table with date ranges in. He wants to find the ranges between the start date and end date that aren't present in the table. — Tanner, Apr 06 '17 at 07:38
@Tanner that's not enough. Why 1-4 and 19-20 and not 17 and 21-24 too? — Panagiotis Kanavos, Apr 06 '17 at 07:39
@PanagiotisKanavos because 17 is within the range of row 2 (16-18) and 21-24 is in the range contained within row 3 (21-25). — Tanner, Apr 06 '17 at 07:40
@PanagiotisKanavos ok, he may have gaps and islands confused, but i assume he's not trying to use the term gaps in the technical way we're discussing. The desired output indicates what he's after though. — Tanner, Apr 06 '17 at 07:44
@k-s, what do you mean by 2 more records? Only missing dates are 2017-05-01 to 2017-05-04 and 2017-05-19 to 2017-05-20. — Rajesh Bhat, Apr 06 '17 at 07:51
If you see startdate and enddate param - we need to consider gap and remaining dates in trailing table. — k-s, Apr 06 '17 at 07:52
Between '2017-05-01' and '2017-05-25' - we have 2 date gaps in below table as described and I want 5 records including 3 gaps already available. — k-s, Apr 06 '17 at 07:53
@k-s - so you want to return the entire #tmpdates table + the two other gaps? — Fredrik Lundin, Apr 06 '17 at 07:56
I do this, unfortunately, in MySQL and the solution won't work directly in MSSQL. But the idea is that you first create a temp table with a row for each date in the range. You then select the table with your "gaps" in and left join the temp table. I'll see if I can find something for MSSQL — Dave Becker, Apr 06 '17 at 08:01
Have a read of this: http://stackoverflow.com/questions/7812986/easiest-way-to-populate-a-temp-table-with-dates-between-and-including-2-date-par it describes how to generate rows for all dates between `@StartDate` and `@EndDate`. Once you have this `LEFT JOIN` the table with the gaps in. — Dave Becker, Apr 06 '17 at 08:05
@DaveBecker it's similar, but this table has 2 columns to define the range. so slightly different — Tanner, Apr 06 '17 at 08:08
Do you really have to do this in the database? Probably way cleaner and easier to do it in code, if possible @k-s — Fredrik Lundin, Apr 06 '17 at 08:09
Any luck on this complex tsql? it seems easy from output though!! — k-s, Apr 06 '17 at 08:20
It is easier to join your table with a table of dates. I don't know about performance though. — Salman A, Apr 06 '17 at 12:02

ahoxha · Accepted Answer · 2017-04-06T11:17:20.173

This works under the assumption that there are no overlapping intervals.

declare @startdate datetime = '2017-05-16'
declare @enddate datetime = '2017-05-26'

create table #tmpdates (id int, date1 datetime, date2 datetime, rate int)

insert into #tmpdates values (0, '2017-04-01', '2017-04-25',22)
insert into #tmpdates values (1, '2017-05-05', '2017-05-15', 10)
insert into #tmpdates values (2, '2017-05-16', '2017-05-18', 12)
insert into #tmpdates values (3, '2017-05-21', '2017-05-25', 15)

declare @final_result table (date1 date, date2 date, rate int)

insert into @final_result 

select @startdate,dateadd(day,-1,t.date1),null
from #tmpdates t
where @startdate < t.date1 and 
        t.date1 <= (select min(t1.date1) from #tmpdates t1 where t1.date1 >= @startdate)

union all

select date1, date2, rate 
from #tmpdates 
where (date1 >= @startdate or date2 >= @startdate) and 
      (date2 <= @enddate or date1 <= @enddate)

union all

select dateadd(day,1,t.date2), 
        ( select dateadd(day,-1,min(t3.date1)) 
            from #tmpdates t3 where t3.date1 > t.date2) , 
        null
from #tmpdates t
where dateadd(day,1,t.date2) < (select min(t1.date1) from #tmpdates  t1 where t1.date1 > t.date2)
and t.date1 >= @startdate and t.date2 <= @enddate

union all

select dateadd(day,1,max(t.date2)), @enddate, null
from #tmpdates t
having max(t.date2) < @enddate


drop table #tmpdates

select * from @final_result order by date1

EDIT

It collects data from four queries and does a union all.

The first query:

select @startdate,dateadd(day,-1,t.date1),null
from #tmpdates t
where @startdate < t.date1 and 
        t.date1 <= (select min(t1.date1) from #tmpdates t1 where t1.date1 >= @startdate)

Selects the gap between the @startdate and the first (smallest) date in the table, if there are intervals before the @startdate they are ignored. So it selects the gap, if there's any, from @startdate to the first date of the interval that is greater than @startdate.

The second query:

select date1, date2, rate 
from #tmpdates 
where (date1 >= @startdate or date2 >= @startdate) and 
      (date2 <= @enddate or date1 <= @enddate)

Selects the records from the table (non-gaps). If the @startdate falls between the range, that record is included. Same happens with the @enddate parameter.

The third query:

select dateadd(day,1,t.date2), 
        ( select dateadd(day,-1,min(t3.date1)) 
            from #tmpdates t3 where t3.date1 > t.date2) , 
        null
from #tmpdates t
where dateadd(day,1,t.date2) < (select min(t1.date1) from #tmpdates  t1 where t1.date1 > t.date2)
and t.date1 >= @startdate and t.date2 <= @enddate

selects gaps between the smallest and the largest (that fall between @startdate and @enddate) intervals on the table.

And finally the fourth query:

select dateadd(day,1,max(t.date2)), @enddate, null
from #tmpdates t
having max(t.date2) < @enddate

Selects the gap between the largest date (the largest between @startdate and @enddate) on the table and @enddate, if there's a gap.

All these records are inserted into the @final_result table, so that they can be ordered by interval.

will this just work for the sample data or will it work with real world larger data sets? — Tanner, Apr 06 '17 at 08:29
@Tanner I added explanations of how it works. It should work for any data set as long as there's no overlapping of intervals (I didn't test with this). — ahoxha, Apr 06 '17 at 08:30
If I pass declare startdate datetime = '2017-05-01', declare enddate datetime = '2017-06-25' - last records shows wrong date range. @ahoxha — k-s, Apr 06 '17 at 08:35
If I add declare startdate datetime = '2017-05-06' - it should include its range. — k-s, Apr 06 '17 at 08:39
I tried with startdate = '2017-05-01' and enddate = '2017-06-25' it is adding one record `2017-05-26 | 2017-06-25 | null`. Isn't this want you need? From 2017-05-26 to 2017-06-25 there's a gap. — ahoxha, Apr 06 '17 at 08:42
If I pass declare startdate datetime = '2017-05-01', declare enddate datetime = '2017-06-25' - last records shows wrong date range - then 2017-05-26 to 2017-06-25 should return NULL. — k-s, Apr 06 '17 at 08:43
Also, If I add declare startdate datetime = '2017-05-06' - it should include its range. — k-s, Apr 06 '17 at 08:43
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/140044/discussion-between-ahoxha-and-k-s). — ahoxha, Apr 06 '17 at 08:44
declare startdate datetime = '2017-05-01' declare enddate datetime = '2017-05-04' - If i pass, it should return 1 record. — k-s, Apr 06 '17 at 09:08

Praveen ND · Answer 2 · 2017-04-06T10:47:38.013

Please make use of below Query:

DECLARE @STARTDATE DATE = '2017-05-01'
DECLARE @ENDDATE DATE = '2017-05-25'

DECLARE @DATES TABLE (ID INT, DATE1 DATE, DATE2 DATE, RATE INT)

INSERT INTO @DATES VALUES 
(1, '2017-05-05', '2017-05-15', 10),
(2, '2017-05-16', '2017-05-19', 12),
(3, '2017-05-21', '2017-05-25', 15)

SELECT* FROM 
(
    SELECT @STARTDATE AS DATE1,DATEADD(DAY,-1,MIN(DATE1)) AS DATE2,'NO DATA'AS RATE FROM @DATES
    UNION
    SELECT 
    CASE WHEN   
            LEAD(DATE1) OVER (ORDER BY DATE1) = DATEADD(DAY,1,DATE2) THEN NULL 
            ELSE DATEADD(DAY,1,DATE2) END AS DATE1,
    CASE WHEN   
            LEAD(DATE1) OVER (ORDER BY DATE1) = DATEADD(DAY,1,DATE2) THEN NULL 
            ELSE LEAD(DATEADD(DAY,-1,DATE1)) OVER (ORDER BY DATE1) END AS DATE2,
    'NO DATA'AS RATE
    FROM @DATES d
    UNION
    SELECT DATE1, DATE2,CAST(RATE AS NVARCHAR(10)) FROM @DATES
    UNION
    SELECT DATEADD(DAY,1,MAX(DATE2)) AS DATE1,@ENDDATE AS DATE2,'NO DATA'AS RATE FROM @DATES
) A WHERE A.DATE2 IS NOT NULL AND A.DATE1 <= A.DATE2
  AND DATE1 >= @STARTDATE AND DATE2 <=@ENDDATE
ORDER BY A.DATE1

This doesn't return one row in case of `@startdate = '2017-05-01'` and `@enddate = '2017-05-04'`, as @k-s wanted. — ahoxha, Apr 06 '17 at 10:31
Hi @ahoxha , Good point. I had just modified the code. You can check the same now. :) — Praveen ND, Apr 06 '17 at 10:48

Rajesh Bhat · Answer 3 · 2017-04-06T09:30:48.097

You can try below code. Am traversing from @StartDate till @endDate and finding the gaps.

declare @startdate datetime = '2017-05-01'
declare @enddate datetime = '2017-05-04'
declare @startdate1 datetime, @enddate1 datetime
declare @dates table (date1 date,date2 date)
create table #tmpdates (id int, date1 datetime, date2 datetime, rate int)

insert into #tmpdates values (1, '2017-05-05', '2017-05-15', 10)
insert into #tmpdates values (2, '2017-05-16', '2017-05-18', 12)
insert into #tmpdates values (3, '2017-05-21', '2017-05-25', 15)

select * from #tmpdates where date1 >= @startdate and date2 <= @enddate
set @startdate1=@startdate
while @startdate1<=@enddate
begin
     if not exists(select 1 from #tmpdates where @startdate1 between date1 and date2)
     begin
          if not exists (select 1 from @dates where @startdate1 > date1 and date2 is null)
          begin
               insert into @dates(date1)values(@startdate1)
          end
          else
          begin
               if @startdate1+1>=@enddate
               begin
                   update @dates set date2=@startdate1 where date2 is null
               end
               set @startdate1+=1
          end
     end
     else
     begin 
         update @dates set date2=@startdate1-2 where date2 is null
     end
     set @startdate1+=1       
end
select * from
(select date1,date2, rate from #tmpdates
union
select *,0  as rate from @dates
) A WHERE date1>=@startdate and date2<=@enddate
drop table #tmpdates

Find gaps in date ranges - TSQL

3 Answers3