Finding the starting ID in log table

• Chrissy321

  SSCoach

  Points: 15766

  More actions

  May 17, 2019 at 10:03 pm

  #3644841

  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: 24398

  More actions

  May 18, 2019 at 6:40 am

  #3644924

  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
  

  ---

  How to post data/code on a forum to get the best help.