Nevyn (3/4/2015)
For starters:
SELECT PERSID,
CASE MAX(CASE DUESTATUS
WHEN 'Overdue' THEN 2
WHEN 'DUE' THEN 1
ELSE 0
END)
WHEN 2 THEN 'Overdue'
WHEN 1 THEN 'Due'
ELSE 'Unknown'
END DUESTATUS,
CASE MAX(CASE COMPSTATUS
WHEN 'Incomplete' THEN 2
WHEN 'Not Started' THEN 1
ELSE 0
END)
WHEN 2 THEN 'Incomplete'
WHEN 1 THEN 'Not Started'
ELSE 'Unknown'
END COMPSTATUS
FROM PERSON
GROUP BY PERSID
Since you are only dealing with one table and you are scanning it it will be tough to get it much more efficient than that.
Nice query Nevyn!! Doubly-nested CASE with MAX - I love it! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service