min max out of diff sequential groups in single group

user_18020301 0
Viewed 42

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

  1. 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

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

Please share suggestions.

oracle sql
2 Answers

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);
user_18632290

The solution from Tim is perfectly fine but this could be simplified to -

[SELECT id, MIN(dt), MAX(dt)
  FROM (SELECT id, dt, dt - ROW_NUMBER() OVER(ORDER BY dt) rn
          FROM your_table)
 GROUP BY id, rn
 ORDER BY id, rn][1]

Demo.

user_36277560
Related