Return to Answer

• Detect changes between row values—read the See It For Yourself section.
• Or this simpler explanation

• Detect changes between row values—read the See It For Yourself section.
• Or this simpler explanation

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
;

See this work at DB Fiddle

• Detect changes between row values—read the See it For Yourself section.
• Or this simpler explanation

You can do this as a simple subtraction of ROW_NUMBER() operations (or if your dates are not unique, though still unique per id_type, then you can use DENSE_RANK() instead, though it will be a more expensive query):

WITH IdTypes AS (
   SELECT
      date,
      id_type,
      Row_Number() OVER (ORDER BY date)
         - Row_Number() 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
;

See this work at DB Fiddle (or see the DENSE_RANK version)

• Detect changes between row values—read the See It For Yourself section.
• Or this simpler explanation

You can do this as a simple subtraction of DENSE_RANK() operations:

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):