Combine continuous datetime intervals by type

Question

Say we have such a table: ``` declare @periods table ( s date, e date, t tinyint ); ``` with date intervals without gaps ordered by start date (s) ``` insert into @periods values ('2013-01-01' , '2013-01-02', 3), ('2013-01-02' , '2013-01-04', 1), ('2013-01-04' , '2013-01-05', 1), ('2013-01-05' , '2013-01-06', 2), ('2013-01-06' , '2013-01-07', 2), ('2013-01-07' , '2013-01-08', 2), ('2013-01-08' , '2013-01-09', 1); ``` All date intervals have different types (t). It is required to combine date intervals of the same type where they are not broken by intervals of the other types (having all intervals ordered by start date). So the result table should look like: ``` s | e | t ------------|------------|----- 2013-01-01 | 2013-01-02 | 3 2013-01-02 | 2013-01-05 | 1 2013-01-05 | 2013-01-08 | 2 2013-01-08 | 2013-01-09 | 1 ``` Any ideas how to do this without cursor? I've got one working solution: ``` declare @periods table ( s datetime primary key clustered, e datetime, t tinyint, period_number…

Accepted Answer

Since your ranges are continuous, the problem essentially becomes a gaps-and-islands one. If only you had a criterion to help you to distinguish between different sequences with the same `t` value, you could group all the rows using that criterion, then just take `MIN(s), MAX(e)` for every group.

One method of obtaining such a criterion is to use two `ROW_NUMBER` calls. Consider the following query:

```
SELECT
  *,
  rnk1 = ROW_NUMBER() OVER (               ORDER BY s),
  rnk2 = ROW_NUMBER() OVER (PARTITION BY t ORDER BY s)
FROM @periods
;
```

For your example it would return the following set:

```
s           e           t   rnk1  rnk2
----------  ----------  --  ----  ----
2013-01-01  2013-01-02  3   1     1
2013-01-02  2013-01-04  1   2     1
2013-01-04  2013-01-05  1   3     2
2013-01-05  2013-01-06  2   4     1
2013-01-06  2013-01-07  2   5     2
2013-01-07  2013-01-08  2   6     3
2013-01-08  2013-01-09  1   7     3
```

The interesting thing about the `rnk1` and `rnk2` rankings is that if you subtract one from the other, you will get values that, together with `t`, uniquely identify every distinct sequence of rows with the same `t`:

```
s           e           t   rnk1  rnk2  rnk1 - rnk2
----------  ----------  --  ----  ----  -----------
2013-01-01  2013-01-02  3   1     1     0
2013-01-02  2013-01-04  1   2     1     1
2013-01-04  2013-01-05  1   3     2     1
2013-01-05  2013-01-06  2   4     1     3
2013-01-06  2013-01-07  2   5     2     3
2013-01-07  2013-01-08  2   6     3     3
2013-01-08  2013-01-09  1   7     3     4
```

Knowing that, you can easily apply grouping and aggregation. This is what the final query might look like:

```
WITH partitioned AS (
  SELECT
    *,
    g = ROW_NUMBER() OVER (               ORDER BY s)
      - ROW_NUMBER() OVER (PARTITION BY t ORDER BY s)
  FROM @periods
)
SELECT
  s = MIN(s),
  e = MAX(e),
  t
FROM partitioned
GROUP BY
  t,
  g
;
```

If you like, you can play with this solution at SQL Fiddle.

Combine continuous datetime intervals by type

Solution

Problem