• Hi,

    I'm a latecomer to the party and didn't realize there had already been healthy debate, but here's what I came up with.

    Looking through the previous submissions above, it's the same concept as Brendan's, but extended to support the full functionality of the original post.

    For the original example (working on an unoptimized temp table) the time taken seems to be approx 0.25 secs/1000 records, so with 10000 records I'm getting something like 3 seconds run time. With the code below there seems to be no increase in time taken at all (around 0.25 secs for anything from 0 to 10000 records), but this is probably because for small datasets the main delay below is not due to the work itself, but rather the creation of the temp table.

    I'd be interested to see if anyone can make something faster! (ignoring the fact that I could probably have made it a table variable...)

    Thanks,

    Tao

     

    CREATE TABLE #StartsAndEnds (EventID Int, EventType NVarChar(20), IdentityTracker INT IDENTITY (1,1))

    INSERT INTO #StartsAndEnds (EventID, EventType)

    SELECT SeqNumber, Type

    FROM (

     SELECT Seq1.SeqNumber, 'Start' AS Type

     FROM #SequenceTable Seq1

     LEFT JOIN #SequenceTable Seq2 ON Seq2.SeqNumber = Seq1.SeqNumber + 1

     WHERE Seq2.SeqNumber Is Null

     

     UNION ALL

     

     SELECT Seq1.SeqNumber, 'End' AS Type

     FROM #SequenceTable Seq1

     LEFT JOIN #SequenceTable Seq2 ON Seq2.SeqNumber = Seq1.SeqNumber - 1

     WHERE Seq2.SeqNumber Is Null

    ) AS PingPong

    ORDER BY SeqNumber

    SELECT IsNull(FL1.EventID, 0) AS StartGap,

     FL2.EventID As EndGap,

     IsNull(FL1.EventID, 0) + 1 AS FirstAvailable,

     FL2.EventID - 1 AS LastAvailable,

     FL2.EventID - IsNull(FL1.EventID, 0) - 1 As AvailableCount

    FROM #StartsAndEnds FL2

    LEFT JOIN #StartsAndEnds FL1 ON FL2.IdentityTracker = FL1.IdentityTracker + 1

    WHERE FL2.IdentityTracker % 2 = 1

    DROP TABLE #StartsAndEnds

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.