TheSQLGuru (3/4/2015)

---

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! 😎