August 27, 2009 at 10:09 am
Hi:
I have the following type of table
Col. A Col. B
stop 12:10
start 12:13
stop 12:34
start 12:37
stop 13:03
start 13:09
start 13:13
stop 13:44
start 13:47
....
I want to get to
Col. A Col. B Col. C
stop to start 12:10 12:13
stop to start 12:34 12:37
stop to start 13:03 13:09
stop to start NULL 13:13
stop to start 13:44 13:47
....
I think this a select within a select, but I get stuck when there are two consecutive 'start' in my original record.
Thoughts?
Using SQL Server 2005, Win2003
Thank you!
August 27, 2009 at 10:40 am
Bullfrog
You are likely to get help if you click on the link in my signature block and read and follow the simple instructions as how to post to assist those who want/can assist you.
August 27, 2009 at 11:17 am
I chucked this together pretty quick but it works as long as there is always a stop time. If there are consecutive stop times then this blows up. I will try to come back to this if needed.
CREATE TABLE #times ([action] VARCHAR(5), [time] dateTIME)
INSERT INTO #times
SELECT 'stop', '12:10'
UNION ALL
SELECT 'start','12:13'
UNION ALL
SELECT 'stop', '12:34'
UNION ALL
SELECT 'start','12:37'
UNION ALL
SELECT 'stop', '13:03'
UNION ALL
SELECT 'start','13:09'
UNION ALL
SELECT 'start', '13:13'
UNION ALL
SELECT 'stop','13:44'
UNION ALL
SELECT 'start','13:47'
;WITH CTE AS
(
SELECT [action], [time], ROW_NUMBER() OVER (PARTITION BY [action] ORDER BY [time]) AS rowNum FROM #times
)
SELECT 'stop to start' AS [action], NULL AS [stopTime], [time] AS startTime
FROM cte cte1
WHERE [action] = 'start'
AND EXISTS (SELECT * FROM CTE cte2 WHERE cte1.rownum = cte2.rowNum AND cte2.[action] = 'stop' AND cte2.[time] > cte1.[time])
union
SELECT 'stop to start' AS [action], [time], (SELECT MIN([time]) FROM #times b WHERE b.[time] > a.[time] AND b.[action] = 'start')
FROM #times a
WHERE a.[action] = 'stop'
ORDER BY [time]
DROP TABLE #times
August 27, 2009 at 12:13 pm
The following works with both multiple start and stop times. It may not be the exact results you want for multiple stop times.
WITH CTE AS
(
SELECT [action], [time], ROW_NUMBER() OVER ( ORDER BY [time] ) AS rowNum FROM #times
)
SELECT a.[action], a.[time], b.[action], b.[time]
FROM CTE AS a
FULL OUTER JOIN CTE AS b
ON a.rowNum = b.rowNum-1
AND a.[action] b.[action]
WHERE IsNull(b.rowNum, 2) > 1
AND IsNull(a.[action], 'stop') = 'stop'
AND IsNull(b.[action], 'start') = 'start'
ORDER BY IsNull(a.[time], b.[time]), b.[time]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 27, 2009 at 12:43 pm
Yep, drew's is much nicer and it handles the logic correctly. I don't even know what I was thinking now that I have read through a much better implementation.
August 28, 2009 at 6:05 am
Great - thanks a bunch guys!
I never would've figured it out, but now that I see what you did it makes sense.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply