How to select a record with looping condition in SQL

Question

I have a table with set of tasks as below. I would like to find out which WOID has all the work tasks completed ( T01,T02,T03) and with any of the admin tasks not completed( T04, T05) which means Tasks T01,T02 & T02 must be in complete state and either Task T04 or T05 in a non complete state. My query would return result WOID W02 as it matches the criteria. I'm having hard time to have above logics in my query.

Task ID	Status	WOID
T01	Complete	W01
T02	Pending	W01
T03	Complete	W01
T04	Pending	W01
T05	Pending	W01
T01	Complete	W02
T02	Complete	W02
T03	Complete	W02
T04	Complete	W02
T05	Pending	W02

First I tried to find the WOID with all completed tasks then join with another sub query to find out the non completed admin tasks but not getting the expected results. Any help on this would be greatly appreciated.

<code>
select a.WOID from
( select a.TASKID,a.WOID,a.STATUS,row_number()over 
   (partition by a.TASKSID order by a.TASKSID ,a.WOID,a.STATUS )rno  from Task a
Left join Task b on a.TASKSID= b.TASKSID) a 
where rno=1
and  a.status='COMPLETE' and a.TASKSID not in ('T04','T05') </code>

Answer 1

Certainly you don't want looping here, you're writing SQL. Some simple conditional aggregation in the HAVING clause seems to be what you're after:

SELECT WOID
FROM dbo.YourTable
GROUP BY WOID
HAVING COUNT(CASE WHEN TaskID IN ('T01','T02','T03') AND Status = 'Pending' THEN 1 END) = 0
   AND COUNT(CASE WHEN TaskID IN ('T04','T05') AND Status = 'Pending' THEN 1 END) > 0;