| 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.
Is this a school assignment?
Assign rownumber in cte and do a selfjoin, so you can compare on current/prevoius status.
This is not a school assignment. I work somewhere. Can you post a a sample example pls.
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
;