select within a select - how to address nulls

  • 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!

  • 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]

  • 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

  • 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

  • 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.

  • 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