Combine two queries into one - Possible? 1

[clapag22]

I have a table called "CLAIMS". It contains these fields among others:
MemberID
ClaimID
PaidAmt
ProviderID
ServiceDt
PaidDt

A ClaimID can be adjusted and contain multiple lines (for example, a claim is paid at $59, then a second line may take back the amount ($-59) and a third line may repay the claim at $49). A ClaimID can also contain a single line with a PaidAmt of $0 for a denied service.

I am often asked to for three pieces of info in based on data in this table:
Total Paid Amount (sum PaidAmt)
Distinct Members Receiving Services count(distinct MemberID)
Distinct Services count(distinct MemberID + convert(varchar, ServiceDt))

When I am asked for total paid amount I obviously want to include negative amounts so the total sums correctly. But when I am asked for distinct members receiving services and/or distinct services, I only want to return the counts based on PAID services. Right now I do this in two queries:

--Total Paid Amount
select sum(paidamt) as TotalPaidAmt
from claims
where servicedt between '1/1/05' and '3/31/05'

--Distinct Members Receiving Services & Distinct Services
select count(distinct MemberID) as MbrsRecSrvc, count(distinct MemberID + convert(varchar, ServiceDt)) as Services
from claims
where servicedt between '1/1/05' and '3/31/05'
and paidamt > 0

Basically my question comes down to is there a way to do this in one query using some sort of case statement on the distinct members and distinct services calculations.

Thanks!

 

[JamesLean]

Try this:

SELECT SUM(paidamt) AS TotalPaid,
  COUNT(DISTINCT CASE WHEN paidamt > 0 THEN memberid ELSE NULL END) AS DistinctMembers,
  COUNT(DISTINCT CASE WHEN paidamt > 0 THEN memberid + CONVERT(varchar, servicedt) ELSE NULL END) AS DistinctServices
FROM claims
WHERE servicedt >= '20050101'
  AND servicedt < '20050401'

--James

 

[clapag22]

Perfect! Thanks!