I have a Table like this:
x
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?
Advertisement
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