May 27, 2016 at 1:21 pm
I'm trying to write a query to turn this record set:
eventtypeeventdatetime
start 2016-05-17 08:00:00.000
start 2016-05-17 09:10:00.000
start 2016-05-17 09:40:00.000
stop 2016-05-17 08:10:00.000
stop 2016-05-17 09:12:00.000
stop 2016-05-17 09:50:00.000
into
starttime endtime
2016-05-17 08:00:00.000 2016-05-17 08:10:00.000
2016-05-17 09:10:00.000 2016-05-17 09:12:00.000
2016-05-17 09:40:00.000 2016-05-17 09:50:00.000
and, of course, keeping them in sequential order.
I know there are several ways to accomplish this, but I'm looking for something simple/elegant that can be used in a CTE (i.e.-no ORDER BY clause), if possible.
Any thoughts?
May 27, 2016 at 2:11 pm
Well, I guess I answered my own question.. unless someone can offer a better solution:
with a as (
select 'start' [eventtype],cast('2016-05-17 08:00:00' as datetime) [eventdatetime]
union select 'start',cast('2016-05-17 09:10:00' as datetime)
union select 'start',cast('2016-05-17 09:40:00' as datetime)
union select 'stop',cast('2016-05-17 09:50:00' as datetime)
union select 'stop',cast('2016-05-17 09:12:00' as datetime)
union select 'stop',cast('2016-05-17 08:10:00' as datetime)
union select 'start',cast('2016-05-17 09:40:00' as datetime)
union select 'stop',cast('2016-05-17 09:50:00' as datetime)
union select 'stop',cast('2016-05-17 09:12:00' as datetime)
), b as (
select
[eventdatetime]
, [eventtype]
, row_number() over (partition by [eventtype] order by [eventdatetime]) [rowId]
from a
), c as (
select
(select [eventdatetime] from b as b2 where b2.[eventtype] = 'start' and b1.[rowId] = b2.[rowId]) as [startdate]
, (select [eventdatetime] from b as b3 where b3.[eventtype] = 'stop' and b1.[rowId] = b3.[rowId]) as [stopdate]
from b as b1
where [eventtype] = 'start'
) select * from c
May 28, 2016 at 2:05 am
This can be done more efficiently with a simple ROW_NUMBER implementation.
😎
The problem with your solution is that it scans the table 1+(2 x number of rows) times, fear it will be very inefficient on larger sets.
Single table scan solution
;with SAMPLE_DATA as (
select 'start' [eventtype],cast('2016-05-17 08:00:00' as datetime) [eventdatetime]
union select 'start',cast('2016-05-17 09:10:00' as datetime)
union select 'start',cast('2016-05-17 09:40:00' as datetime)
union select 'stop',cast('2016-05-17 09:50:00' as datetime)
union select 'stop',cast('2016-05-17 09:12:00' as datetime)
union select 'stop',cast('2016-05-17 08:10:00' as datetime)
union select 'start',cast('2016-05-17 09:40:00' as datetime)
union select 'stop',cast('2016-05-17 09:50:00' as datetime)
union select 'stop',cast('2016-05-17 09:12:00' as datetime)
union select 'start',cast('2016-05-17 10:00:00' as datetime)
union select 'stop',cast('2016-05-17 10:50:00' as datetime)
union select 'start',cast('2016-05-17 10:40:00' as datetime)
union select 'stop',cast('2016-05-17 11:50:00' as datetime)
)
,GROUPED_DATA AS
(
SELECT
(ROW_NUMBER() OVER
(
ORDER BY SD.eventdatetime
) / 2) +
CASE
WHEN SD.eventtype = 'start' THEN 1
ELSE 0
END AS GRP
,SD.eventtype
,SD.eventdatetime
FROM SAMPLE_DATA SD
)
SELECT
MAX(CASE WHEN GB.eventtype = 'start' THEN GB.eventdatetime END) AS starttime
,MAX(CASE WHEN GB.eventtype = 'stop' THEN GB.eventdatetime END) AS endtime
FROM GROUPED_DATA GB
GROUP BY GB.GRP;
Output
starttime endtime
----------------------- -----------------------
2016-05-17 08:00:00.000 2016-05-17 08:10:00.000
2016-05-17 09:10:00.000 2016-05-17 09:12:00.000
2016-05-17 09:40:00.000 2016-05-17 09:50:00.000
2016-05-17 10:00:00.000 2016-05-17 10:50:00.000
2016-05-17 10:40:00.000 2016-05-17 11:50:00.000
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply