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!