i'm trying to better task scheduler software monitoring by querying a table, with column events, contains steps of tasks scheduler log: task initiated, task stopped, task triggered manually, etc

now i know that if a specific order of events is met, that the task was executed fully successfully

say that the optimal order of events for a task is

• task started
• task processing
• job task finished

now if for a particular task, i have the following order of events:

• task started
• start triggered manually
• task processing job
• task finished

this order of event is not optimal, because event start triggered manually is not part of the optimal order of events - i want to flag this task

now if for a particular task, the following order of events occurr:

• task started
• task finished

this order of event is not optimal, because event task processing job is missing, i want to flag this task The optimal order of event I get using the following query

   select t.events from 
    (SELECT distinct events FROM [jobmonitoring]) t
    ORDER BY (case when activity = 'task started' then 1
                  when activity = 'task processing job' then 2
                  when activity = 'task finished' then 3
                  else 4 end)

i'm stuck in flagging the tasks that do not follow this particular order of events flagging must respect, these 3 events in that specific order

desired output would look something like

tasks c and e do not follow optimal event ordering

table jobmonitoring, looks like this

CodePudding user response：

You can add a ROW_NUMBER then group by task and use conditional aggregation

SELECT
  t.task,
  flag = CASE WHEN MAX(CASE WHEN t.rn = 1 THEN t.events END) <> 'task started'
                OR MAX(CASE WHEN t.rn = 2 THEN t.events END) <> 'task processing'
                OR MAX(CASE WHEN t.rn = 3 THEN t.events END) <> 'job task finished'
         THEN 'flagged' END
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY t.task ORDER BY t.timestamp)
    FROM YourTable t
) t
GROUP BY
  t.task;

db<>fiddle