CODEWITHSUNDEEP
sql-server
djohnjohn
djohnjohn

Reputation: 65

sql server check if ordering is met

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

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

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:

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

task flag
a null
b null
c flagged
d null
e flagged

tasks c and e do not follow optimal event ordering

table jobmonitoring, looks like this

task events timestamp
c task started 28072022 1205
c job task finished 28072022 1305
e task started 28072021 1005
e job task finished 28072021 1105
e task processing 28072021 1205
a task started 21072021 0905
a task processing 21072021 1005
a job task finished 21072021 1205

Upvotes: 1

Views: 47

Answers (1)

Charlieface
Charlieface

Reputation: 72128

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

Upvotes: 2

Related Questions