min max out of diff sequential groups in single group-CodePudding

Is there any way to get min/max out of diff sequential groups present in single group ? I want to get min/max out of all sequential groups in a group.

Here is the input data

id  dt
1   11-01-22
1   12-01-22
1   13-01-22
1   15-01-22
1   19-01-22
1   20-01-22
1   01-01-22
1   02-01-22
1   03-01-22
1   04-01-22
1   08-01-22

expected output

Id, min    , max
1, 01-01-22, 04-01-22
1, 08-01-22, 08-01-22
1, 11-01-22, 13-01-22
1, 15-01-22, 15-01-22
1, 19-01-22, 20-01-22

Trying with this query

select 
    id, dt, 
    case when dt - 1 = lag(dt) over (partition by id order by dt) then 0 else 1 end as mark
  from consecutive
order by dt

using this am getting two issues

Not able to handle null coming from lag of first date in group ? How to handle records marked with --.

 1   01-01-22    1   --
 1   02-01-22    0
 1   03-01-22    0
 1   04-01-22    0
 1   08-01-22    1  
 1   11-01-22    1--
 1   12-01-22    0
 1   13-01-22    0
 1   15-01-22    1  
 1   19-01-22    1--
 1   20-01-22    0

How to manage If I've more than one Ids in table ?

Please share suggestions.

CodePudding user response：

We can try forming pseudo groups of each set of dates which are in sequence. Then simply aggregate and take the min and max date for each group.

WITH cte AS (
    SELECT t.*, CASE WHEN dt - LAG(dt) OVER (PARTITION BY id ORDER BY dt) > 1
                     THEN 1 ELSE 0 END AS val
    FROM yourTable t
),
cte2 AS (
    SELECT t.*, SUM(val) OVER (PARTITION BY id ORDER BY dt) AS grp
    FROM cte t
)

SELECT id, MIN(dt) AS min_dt, MAX(dt) AS max_dt
FROM cte2
GROUP BY id, grp
ORDER BY id, MIN(dt);