Join with case query

1

Hi,

I have a Claims table, that has a ClaimID as PK.
I have a Services table, that has ServiceID as PK and Claims.ClaimID as FK.
Each Claim can have 1:many Services underneath.

Each Services entry has a Status field which can be: Approved or Denied.

How can I find below?

Total # of Claims that have all the underneath Services Approved?
Total # of Claims that have all the underneath Services Denied?
Total # of Claims that have partially approved Services underneath (>= 1 Approved and >= 1 Denied)?

I think it would be some type of CASE statement with table join but unable to come up with.
I have taken Claims table ClaimID in separate table to simplify the process, but could not get it working.

Any help is greatly appreciated. Thank you!

2 
SELECT
    SUM(All_Services_Approved) AS All_Services_Approved_Count,
    SUM(All_Services_Denied) AS All_Services_Denied_Count,
    SUM(Mixed_Approved_And_Denied) AS Mixed_Approved_And_Denied_Count
FROM (
SELECT
    CASE WHEN COUNT(c.ClaimID) = SUM(CASE WHEN s.Status = 'Approved' THEN 1 ELSE 0 END) THEN 1 ELSE 0 END 
        AS All_Services_Approved,
    CASE WHEN COUNT(c.ClaimID) = SUM(CASE WHEN s.Status = 'Denied' THEN 1 ELSE 0 END) THEN 1 ELSE 0 END 
        AS All_Services_Denied,
    CASE WHEN MAX(CASE WHEN s.Status = 'Approved' THEN 1 ELSE 0 END) >= 1 AND
              MAX(CASE WHEN s.Status = 'Denied' THEN 1 ELSE 0 END) >= 1 THEN 1 ELSE 0 END AS Mixed_Approved_And_Denied
FROM Claims c
LEFT OUTER JOIN Services s ON s.ClaimID = c.ClaimID
) AS derived