Поиск подряд идущих дат (Островов)

Задача которая в том или ином виде встает перед многими. Как набор дат разбить на группы подряд идущих дней? Нередко для обозначения таких групп используется слово острова (Islands).
Итак, имеем таблицу с исходными данными.

CREATE TABLE [Data] (
[Date] [datetime] NOT NULL
);
GO
insert into [Data] ([Date])
      select convert(datetime,'01.04.2012',104)
union select convert(datetime,'02.04.2012',104)
union select convert(datetime,'31.03.2012',104)

union select convert(datetime,'09.05.2012',104)
union select convert(datetime,'10.05.2012',104)

union select convert(datetime,'31.12.2012',104)
union select convert(datetime,'01.01.2013',104)

И хотим в итоге получить следующие результат

Решение

Первое, что мы должны сделать это выбрать начальную отметку(дату). Мы можем это сделать с помощью CTE, или выбрать любую произвольную дату.

;with [StartDate] as (
  select
    min([Date]) [SomeDate]
  from [Data]
)
--или
DECLARE @SomeDate datetime;
SET @SomeDate = convert(datetime,'01.01.2000',104);

Теперь для каждой даты найдем разницу между текущей датой начальной отметкой

select [Date], DATEDIFF(day, @SomeDate, [Date]) [diff]
from [Data]

Получаем следующие данные.

Теперь пронумеруем каждую строку с помощью функции ROW_NUMBER. В нашем случае мы имеем дело с однородными данными, поэтому достаточно сделать сквозную нумерацию. Но если бы таблица содержала данные для разных объектов, и острова нужно было бы найти для каждого объекта в отдельности, то необходимо бы было для каждого объекта делать отдельную нумерацию.

select
   [Date], DATEDIFF(day, @SomeDate, [Date]) [diff]
  ,ROW_NUMBER() Over(order by [Date]) [index]
from [Data]

Что бы увидеть закономерность достаточно вычислить разность между полями [diff] и [index].

select 
   [Date]
  ,DATEDIFF(day, @SomeDate, [Date])
      - ROW_NUMBER() Over(order by [Date]) [island]
from [Data]

Осталось сгруппировать по полю [island] и вычислить минимальную и максимальную дату для каждого острова.

;with [Islands] as (
  select 
     [Date]
    ,DATEDIFF(day, @SomeDate, [Date])
        - ROW_NUMBER() Over(order by [Date]) [island]
  from [Data]
)
select 
   min([Date]) [from]
  ,max([Date]) [to]
from [Islands]
group by [island]
order by [island]

На самом деле все по другому..

Ниже приведены несколько случаев усложняющих нашу задачу.

Данные привязаны к разным объектам.

Как отмечено выше, мы рассматривали ситуацию, когда все данные из таблице [Data] относятся к одному объекту. Что делать, если даты распределены по разным объектам. Таким образом, задача сводится к поиску островов для каждого объекта в отдельности. То есть таблица [Data] имеет следующее вид.

CREATE TABLE [Data] (
     [objectId] [int] NOT NULL,
     [Date] [datetime] NOT NULL
);
GO
      select 1, convert(datetime,'01.04.2012',104)
union select 1, convert(datetime,'02.04.2012',104)
union select 1, convert(datetime,'31.03.2012',104)

union select 2, convert(datetime,'09.05.2012',104)
union select 2, convert(datetime,'10.05.2012',104)

union select 1, convert(datetime,'31.12.2012',104)
union select 1, convert(datetime,'01.01.2013',104)

Тогда запрос нужно будет модифицировать следующим образом.

;with [Islands] as (
  select 
     [objectId]
    ,[Date]
    ,DATEDIFF(day, @SomeDate, [Date])
        - ROW_NUMBER Over(partition by [objectId] order by [Date]) [island]
  from [Data]
)
select 
   [objectId]
  ,min([Date]) [from]
  ,max([Date]) [to]
from [Islands]
group by [objectId], [island]
order by [objectId], [island]

Дублирующиеся даты

Так же можно заметить, что скрипт работает правильно только тогда, когда даты уникальны для каждого объекта. Понятно, что так бывает не всегда. Чтобы обработать этот случай, нужно использовать другую ранжирующую функцию, а именно DENSE_RANK. Тогда наш скрипт будет выглядеть следующим образом.

;with [Islands] as (
  select 
     [objectId]
    ,[Date]
    ,DATEDIFF(day, @SomeDate, [Date])
        - DENSE_RANK() Over(partition by [objectId] order by [Date]) [island]
  from [Data]
)
select 
   [objectId]
  ,min([Date]) [from]
  ,max([Date]) [to]
from [Islands]
group by [objectId], [island]
order by [objectId], [island]

Заключение

Данный скрипт интересен не только как решение конкретной задачи, но и как демонстрация подхода, который может быть применен к целому классу проблем.