Group Date ranges with separate groups if other date in between

Question

I have a Table like this:

Specification    Date 
ADS              2020-09-04 00:00:00.000
ADS              2020-09-05 00:00:00.000
ADS              2020-09-06 00:00:00.000
AZR              2020-09-07 00:00:00.000
AZR              2020-09-08 00:00:00.000
ADS              2020-09-09 00:00:00.000
ADS              2020-09-10 00:00:00.000

I want to select an Start Date and an End date and group them by Specification. So my Output should look like this:

Specification    Start                     End
ADS              2020-09-04 00:00:00.000   2020-09-06 00:00:00.000
AZR              2020-09-07 00:00:00.000   2020-09-08 00:00:00.000
ADS              2020-09-09 00:00:00.000   2020-09-10 00:00:00.000

I tried it with:

select
Specification, MIN(Date) as Start, MAX(Date) as End
from CUSTOMERS
group by Specification 

but this doesnt give my desired result. Does anyone have an Idea?

Answer 1

You can try the below - it's a gap & island problem

select col1, min(dateval) as start, max(dateval) as end
from
(
select *,row_number() over(order by dateval)-
row_number() over(partition by col1 order by dateval) as grp
from t
)A group by col1, grp