SQL SELECT using CASE Statement, but multiple criteria

Question

I'm trying to perform a SQL SELECT query using a CASE statement, which is working 100%. My code looks as follows:

SELECT t.TASK_WINDOW, 
   SUM(CASE WHEN t.TASK_NAME = 'DEV' AND t.TASK_STATUS = 'Completed' THEN 1 ELSE 0 END) AS DevComplete,
   SUM(CASE WHEN t.TASK_NAME = 'TEST' AND t.TASK_STATUS = 'Completed' THEN 1 ELSE 0 END) AS TestComplete,
   SUM(CASE WHEN t.TASK_NAME = 'RELEASE' AND t.TASK_STATUS = 'Completed' THEN 1 ELSE 0 END) AS ReleaseComplete
FROM Requirements r
    INNER JOIN Tasks t
        ON r.TASK = t.REQ_ID
GROUP BY t.TASK_WINDOW

However my problem is that I have an additional criteria for all three SUMS. The complexity is that a requirement might have multiple tasks of each type, but for example an requirement must only be Dev Task complete if all its Dev Tasks are complete, otherwise it is incomplete.

I really want to be able to measure the amount of requirements with all its dev tasks complete, and also all its test tasks complete and all its release tasks complete, but group them all against the latest Dev Task compelted's window.

Please help ;-)

Solution

You need to compare the total with the number complete:

SELECT t.TASK_WINDOW,     

CASE
WHEN SUM(CASE WHEN t.TASK_NAME = 'DEV' THEN 1 ELSE 0 END) =
   SUM(CASE WHEN t.TASK_NAME = 'DEV' AND t.TASK_STATUS = 'Completed' THEN 1 ELSE 0 END) 
Then 1 
ELSE 0 
END as AllDevComplete

FROM Requirements r
  INNER JOIN Tasks t
      ON r.TASK = t.REQ_ID
GROUP BY  t.TASK_WINDOW  

Alternatively you could look for the number which are NOT complete. But the same basic trick - a case of a sum of a case - will work.