Using case to Sum

[JazzMaan]

I am trying to achieve this
1 Count Closed cases
2 Open Cases
3 Open Cases - Phase 1
4 Open CAses - Phase 2
5 Open Cases - Phase 1 + 2

Status_id = 1 means case open, status_id=2 means case closed.

I am able to achieve #1-4 with the follwoing query:



SELECT
case_subject AS [SUBJECT],
COUNT(*) AS [TOTAL CASES],
SUM(CASE STATUS_ID WHEN 1 THEN 1 ELSE 0 END) AS [OPEN CASES],
SUM(CASE STATUS_ID WHEN 2 THEN 1 ELSE 0 END) AS [CLOSED CASES],
SUM(CASE PHASE1_STATUS_ID WHEN 1 THEN 1 ELSE 0 END) AS [PHASE 1 OPEN],
SUM(CASE PHASE2_STATUS_ID WHEN 1 THEN 1 ELSE 0 END) AS [PHASE 2 OPEN]

FROM
CASE_Table

GROUP BY
case_subject
ORDER BY
case_subject



How can i acheive: Open Cases - Phase 1 + 2

 

[PHV]

Have you tried this ?
SUM(CASE (PHASE1_STATUS_ID+PHASE2_STATUS_ID) WHEN 2 THEN 1 ELSE 0 END) AS [PHASE 1+2 OPEN]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JazzMaan]

Many Thanks.

That works.