merge consecutive dates ranges using SQL-CodePudding

i want to find the end date of all the consecutive date ranges. Some of the dates are not consecutive, in this case it will return the end of the single range.

Table Name: Sospensioni

ClientId.    Status.  StartDate  EndDate   
   1           1     01/01/2022  02/01/2022
   1           1     03/01/2022  04/01/2022
   1           1     12/01/2022  15/01/2022
   2           1     03/01/2022  03/01/2022
   2           1     05/01/2022  06/01/2022

i want a sql statement to merge consecutive ranges for each client (example of result)

ClientId.    Status.  StartDate  EndDate   
   1           1     01/01/2022  04/01/2022
   1           1     12/01/2022  15/01/2022
   2           1     03/01/2022  03/01/2022
   2           1     05/01/2022  06/01/2022

I want to solve the problem using SQL only. thanks

CodePudding user response：

This is a Gaps & Islands problem. You can use the typical solution using LAG(). For example:

select
  max(client_id) as client_id,
  max(status) as status,
  min(start_date) as start_date,
  max(end_date) as end_date
from (
  select *, sum(i) over(partition by client_id order by start_date) as g
  from (
    select *,
      case when dateadd(day, -1, start_date) <> 
        lag(end_date) over(partition by client_id order by start_date) 
      then 1 else 0 end as i
    from t
  ) x
) y
group by client_id, g
order by client_id, g

Result:

 client_id  status  start_date  end_date   
 ---------- ------- ----------- ---------- 
 1          1       2022-01-01  2022-01-04 
 1          1       2022-01-12  2022-01-15 
 2          1       2022-01-03  2022-01-03 
 2          1       2022-01-05  2022-01-06

See running example at db<>fiddle.