Найти пробелы в диапазонах дат - TSQL

Ответ 1

Это работает в предположении, что нет перекрывающихся интервалов.

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

ИЗМЕНИТЬ

Он собирает данные из четырех запросов и выполняет union all.

Первый запрос:

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)

Выбирает пробел между @startdate и первой (наименьшей) датой в таблице, если есть интервалы перед @startdate, они игнорируются. Таким образом, он выбирает пробел, если он есть, от @startdate до первой даты интервала, превышающего @startdate.

Второй запрос:

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

Выбор записей из таблицы (без пробелов). Если @startdate находится между диапазоном, эта запись включена. То же самое происходит с параметром @enddate.

Третий запрос:

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

выбирает промежутки между наименьшими и наибольшими (которые находятся между @startdate и @enddate) интервалами в таблице.

И наконец четвертый запрос:

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

Выбор промежутка между наибольшей датой (наибольшей между @startdate и @enddate) в таблице и @enddate, если есть пробел.

Все эти записи вставляются в таблицу @final_result, поэтому их можно упорядочить по интервалу.

Ответ 2

Пожалуйста, используйте ниже запрос:

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 <[email protected]
ORDER BY A.DATE1

Ответ 3

Вы можете попробовать под кодом. Переход от @StartDate до @endDate и поиск пробелов.

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 @[email protected]
while @startdate1<[email protected]
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>[email protected]
               begin
                   update @dates set [email protected] where date2 is null
               end
               set @startdate1+=1
          end
     end
     else
     begin 
         update @dates set [email protected] 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>[email protected] and date2<[email protected]
drop table #tmpdates