I have source of data in the following format:
Event Type| Date
1 | 2011-07-14 09:00
1 | 2011-07-14 09:01
1 | 2011-07-14 09:02
2 | 2011-07-14 09:30
2 | 2011-07-14 09:31
1 | 2011-07-14 10:00
1 | 2011-07-14 10:01
Event types are sorted by date, as they occurred. I need to make a query which will show the date ranges when events were used, sorted by date. Like this:
Event Type | Date Range
1 | 2011-07-14 09:00 - 2011-07-14 09:02
2 | 2011-07-14 09:30 - 2011-07-14 09:31
1 | 2011-07-14 10:00 - 2011-07-14 10:01
Do you have any hints? I reckon this will probably need to be done with analytic functions, but I haven't been able to come up with a decent solution yet.
You could also try the following approach:
WITH ranked AS (
SELECT
EventType,
Date,
ROW_NUMBER() OVER (ORDER BY Date) -
ROW_NUMBER() OVER (PARTITION BY EventType ORDER BY Date) AS GroupID
FROM Events
)
SELECT
EventType,
MIN(Date) AS StartDate,
MAX(Date) AS EndDate
FROM ranked
GROUP BY
GroupID,
EventType
ORDER BY
MIN(Date)