You can do this as a simple subtraction of DENSE_RANK() operations (or if your dates are guaranteed unique then you can use ROW_NUMBER() in both places for a less expensive query):
WITH IdTypes AS (
SELECT
date,
id_type,
Dense_Rank() OVER (ORDER BY date)
- Dense_Rank() OVER (PARTITION BY id_type ORDER BY date)
AS Seq
FROM
tmp
)
SELECT
Min(date) AS begin,
Max(date) AS end,
id_type
FROM IdTypes
GROUP BY id_type, Seq
ORDER BY begin
;
Result:
begin end id_type
--------------------- --------------------- -------
2017-01-10 07:19:21 2017-01-10 07:19:25 3
2017-01-10 07:19:26 2017-01-10 07:19:26 5
2017-01-10 07:19:27.1 2017-01-10 07:19:27.1 3
2017-01-10 07:19:28 2017-01-10 07:19:29 5
2017-01-10 07:19:30.1 2017-01-10 07:19:30.1 3
2017-01-10 07:19:31 2017-01-10 07:19:31 5
2017-01-10 07:19:32 2017-01-10 07:19:32 3
2017-01-10 07:19:33.1 2017-01-10 07:19:37.1 5
Logically, you can think of this as a simple DENSE_RANK() with a PREORDER BY, that is, you want the DENSE_RANK of all the items that are ranked together, and you want them ordered by the dates, you just have to deal with the pesky problem of the fact that at each change in the date, DENSE_RANK will increment. You do that by using the expression as I showed you above. Imagine if you had this syntax: DENSE_RANK() OVER (PREORDER BY date, ORDER BY id_type) where the PREORDER is excluded from the ranking calculation and only the ORDER BY is counted.
Note that it's important to GROUP BY both the generated Seq column as well as the id_type column. Seq is NOT unique by itself, there can be overlaps--you must also group by id_type.
For further reading on this topic:
- Detect changes between row values—read the See it For Yourself section.
- Or this simpler explanation
That first link gives you some code you can use if you wanted the begin or end date to be the same as the previous or next period's end/begin date (so there are no gaps). Plus other versions that could assist you in your query. Though they have to be translated from SQL Server syntax...