Finding the starting ID in log table

  • Chrissy321

    SSCoach

    Points: 15486

    Thanks if you can help. A logged process starts and then perform additional actions. Then the process starts again.

    I want to group the starting log message and the subsequent messages associated with it so that each message can tie back to it's 'Starting' message ID.

    The last select shows my expected results in column StartingID using a CASE statement.

    I though lag would help me here but I need the previous  'Starting' message not just the previous message.

    	IF OBJECT_ID('tempdb..#Logs') IS NOT NULL DROP TABLE #Logs
    CREATE TABLE #Logs
    (
    LogID INT IDENTITY NOT NULL,
    LogTime DATETIME NOT NULL DEFAULT (GETDATE()),
    LogMessage VARCHAR(20)
    )

    INSERT INTO #Logs (LogMessage) VALUES ('Starting')
    INSERT INTO #Logs (LogMessage) VALUES ('Do thing #1')
    INSERT INTO #Logs (LogMessage) VALUES ('Do thing #2')
    INSERT INTO #Logs (LogMessage) VALUES ('Do thing #3')
    INSERT INTO #Logs (LogMessage) VALUES ('Do thing #4')
    INSERT INTO #Logs (LogMessage) VALUES ('Starting')
    INSERT INTO #Logs (LogMessage) VALUES ('Do thing #1')
    INSERT INTO #Logs (LogMessage) VALUES ('Do thing #2')
    INSERT INTO #Logs (LogMessage) VALUES ('Starting')
    INSERT INTO #Logs (LogMessage) VALUES ('Do thing #1')
    INSERT INTO #Logs (LogMessage) VALUES ('Do thing #2')
    INSERT INTO #Logs (LogMessage) VALUES ('Do thing #3')
    INSERT INTO #Logs (LogMessage) VALUES ('Do thing #4')
    INSERT INTO #Logs (LogMessage) VALUES ('Do thing #5')

    SELECT * FROM #Logs
    ORDER BY
    LogID,
    LogTime

    SELECT
    LogID,
    LogTime,
    LogMessage,
    CASE LogID
    WHEN 1 THEN 1
    WHEN 2 THEN 1
    WHEN 3 THEN 1
    WHEN 4 THEN 1
    WHEN 5 THEN 1
    WHEN 6 THEN 6
    WHEN 7 THEN 6
    WHEN 8 THEN 6
    WHEN 9 THEN 9
    WHEN 10 THEN 9
    WHEN 11 THEN 9
    WHEN 12 THEN 9
    WHEN 13 THEN 9
    WHEN 14 THEN 9
    END
    AS StartingID
    FROM #Logs
    ORDER BY
    LogID,
    LogTime
  • DesNorton

    SSC-Insane

    Points: 22596

    Based on the sample data, this should get you what you want

    WITH cteStarts as (
    SELECT StartID = LogID
    FROM #Logs
    WHERE LogMessage = 'Starting'
    )
    , cteGroups as (
    SELECT StartID
    , EndID = LEAD(StartID) OVER (ORDER BY StartID) -- The last EndID will be NULL
    FROM cteStarts
    )
    SELECT l.*
    , StartingID = g.StartID
    FROM #Logs AS l
    LEFT JOIN cteGroups AS g
    ON l.LogID >= g.StartID
    AND l.LogID < ISNULL(g.EndID, l.LogID+1); -- Cater for the NULL EndID

Viewing 2 posts - 1 through 2 (of 2 total)

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