SQLTeam.com | Weblogs | Forums

Mind Boggling query


#1
Task Table
Server ID Action Task ID Status
123 43 1 completed
123 43 3 completed
123 43 2 completed
123 43 4 active
123 43 5 active
123 45 1 active
123 45 4 active
123 45 3 active
123 45 2 active
123 45 5 active
234 43 5 active
234 43 3 active
234 43 4 completed
234 43 2 completed
234 43 1 completed
Task Template
TaskID TaskPredecessor
2 1
3 2
4 2
5 4
6 5
Output
Server ID Action Task ID Status
123 43 1 completed
123 43 3 completed
123 43 2 completed
123 43 4 active
123 45 1 active
234 43 3 active
234 43 4 completed
234 43 2 completed
234 43 1 completed

Preface: The query should look at the combination of the ServerID and Action ID pick up the task ID and check if it has a predecessor. If it has a predecessor then check the server action combination with that predecessor ID with status completed or active. If completed then check next.

Output: The output should print the Server action combination along with task ID and status. Constraint: it should print all the completed task and should stop printing at the latest active task.

ASK: Cant seem to get my head around it. Please post a sample query. Note: No use of cursors.


#2

Is this a school assignment?

Assign rownumber in cte and do a selfjoin, so you can compare on current/prevoius status.


#3

This is not a school assignment. I work somewhere. Can you post a a sample example pls.


#4

Something like this (avoiding tasktemplate - don't know your data, so maybe thats not even an option):

with cte
  as (select serverid
            ,[action]
            ,taskid
            ,[status]
            ,row_number() over(partition by serverid
                                           ,[action]
                                   order by taskid
                              )
             as rn
        from task
     )
select serverid
      ,[action]
      ,taskid
      ,[status]
  from cte as a
 where not exists (select 1
                      from cte as b
                    where b.serverid=a.serverid
                      and b.[action]=a.[action]
                      and b.rn<a.rn
                      and b.[status]!='completed'
                  )
 order by serverid
         ,[action]
         ,taskid
;

or this (including tasktemplate):

    ,cte
  as (select a.serverid
            ,a.[action]
            ,a.taskid
            ,a.[status]
                  ,row_number() over(partition by a.serverid
                                                 ,a.[action]
                                         order by a.taskid
                                    )
                   as rn
        from task as a
             left join tasktemplate as b
                     on b.taskid=a.taskid
             left join task as c
                     on c.serverid=a.serverid
                    and c.[action]=a.[action]
                    and c.taskid=b.taskpredecessor
       where b.taskid is null
          or c.[status]='completed'
     )
select serverid
      ,[action]
      ,taskid
      ,[status]
  from cte as a
 where not exists (select 1
                     from cte as b
                    where b.serverid=a.serverid
                      and b.[action]=a.[action]
                      and b.rn<a.rn
                      and b.[status]!='completed'
                  )
 order by serverid
         ,[action]
         ,taskid
;