SQL: Return Aggregated Results based on some conditions

I need help with making a conclusion in SQL Server based on some column’s values, like status aggregation sort of. As an example, below is a table containing server tasks and their status.

If I want to return the aggregated status of each server here are the rules:

• if all the server tasks are at status ‘SUBMITTED’, then the aggregated server status is ‘AWAITING’
• if all tasks are at status ‘COMPLETED’ – the aggregated status is ‘DONE’
• if the above cases are not met, the aggregated server status is ‘IN PROGRESS’

Example Table: Tasks

Server	Task_Status	Task
Server 1	RUNNING	1-1
Server 1	COMPLETED	1-2
Server 1	SUBMITTED	1-3
Server 2	COMPLETED	2-1
Server 2	COMPLETED	2-2
Server 3	SUBMITTED	3-1
Server 3	SUBMITTED	3-2

Example Query Result:

Server	Completion
Server 1	IN PROGRESS
Server 2	DONE
Server 3	AWAITING

Answer

You could use this:

WITH table_name AS
(
    SELECT 'Server 1' AS server, 'RUNNING' AS task_status, '1-1' AS task UNION ALL
    SELECT 'Server 1' AS server, 'COMPLETED' AS task_status, '1-2' AS task UNION ALL
    SELECT 'Server 1' AS server, 'SUBMITTED' AS task_status, '1-3' AS task UNION ALL
    SELECT 'Server 2' AS server, 'COMPLETED' AS task_status, '2-1' AS task UNION ALL
    SELECT 'Server 2' AS server, 'COMPLETED' AS task_status, '2-2' AS task UNION ALL
    SELECT 'Server 3' AS server, 'SUBMITTED' AS task_status, '3-1' AS task UNION ALL
    SELECT 'Server 3' AS server, 'SUBMITTED' AS task_status, '3-2' AS task 
)

SELECT server,
    CASE 
        WHEN COUNT(DISTINCT task_status) = 1 AND MAX(task_status) = 'SUBMITTED' THEN 'AWAITING'
        WHEN COUNT(DISTINCT task_status) = 1 AND MAX(task_status) = 'COMPLETED' THEN 'DONE'
        ELSE 'IN PROGRESS'
    END AS completion
FROM table_name
GROUP BY server
ORDER BY server;

Tested in sqlfiddle