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?

Answer 1

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.

Answer 2

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