select within a select - how to address nulls

Question

select within a select - how to address nulls

Bullfrog

SSC Eights!

Points: 895
More actions
August 27, 2009 at 10:09 am

#137690

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!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

matt6288 SSCertifiable Points: 5058 More actions · Answer 2

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

drew.allen SSC Guru Points: 76985 More actions · Answer 3

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 4

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.

Bullfrog SSC Eights! Points: 895 More actions · Answer 5

Great - thanks a bunch guys!

I never would've figured it out, but now that I see what you did it makes sense.