I have a Table like this: I want to select an Start Date and an End date and group them by Specification. So my Output should look like this: I tried it with: but this doesnt give my desired result. Does anyone have an Idea? Answer You can try the below - it's a gap & island problem

Group Date ranges with separate groups if other date in between

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

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

Advertisement

Answer