sql syntax case statement

I have a situation in the medical billing world. A patient comes in and has a procedure. That goes into a tables called charges. This patient has a copay and then a potential set of insurance copays. There is a payments table with a pay type column. That column can contain the values of 'CP' for copay, 'MD' for medicare or 'IN' for insurance. So for every charge I need a total of those three potential types even if the amount is zero. My question here is how to I also have three values for each type of payment type. My current case statement breaks down if there was no copayment at all. For example:  
Patient ID : 0001
Visit Number: 00300
Charges $125
CoPay: $0.00
Medicare: $50
Insurance $50

I need to know that the patient owes $25 for the copay. So how do I arrange the sql statement so it will show

0001,00300,125.00,0.00,50.00,50.00 where 0001 is the patient number, 00300 is the visit number, 125.00 is the charge, 0.00 is the amount of copay received,50.00 is medicare amount and 50.00 is the insurance amount.
So in this case the patient never sent in his copay. So regardless of whether there are records in the payment tables for each type of payment, I always want to see a value for all three even if the value is zero.
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
One possible solution is to use subqueries in the select clause:
select PatientNumber, VisitNumber, ISNULL((select amount from payments where type = 'CP' and visitNumber = Charges.PaymentNumber), 0) CoPay, ...
FROM Charges
WHERE ...

Open in new window

0
Scott PletcherSenior DBACommented:
I guess something like this, based on the limited info I/we have:


SELECT
    c.[Patient ID],
    c.[Visit Number],
    SUM(c.Charges) AS Charges, --or much less likely maybe "MAX(c.Charges)"
    SUM(CASE WHEN p.[pay type] = 'CP' THEN p.payment ELSE 0 END) AS CoPay,
    SUM(CASE WHEN p.[pay type] = 'MD' THEN p.payment ELSE 0 END) AS Medicare,    
    SUM(CASE WHEN p.[pay type] = 'IN' THEN p.payment ELSE 0 END) AS Insurance
FROM charges c
LEFT OUTER JOIN payments p ON
    p.[Patient ID] = c.[Patient ID] AND
    p.[Visit Number] = c.[Visit Number]
GROUP BY
    c.[Patient ID],
    c.[Visit Number]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
no points pl.

OR, it may not be necessary to treat [charges].[charges] with an aggregate at all ?
SELECT
    c.[Patient ID],
    c.[Visit Number],
    c.Charges,
    SUM(CASE WHEN p.[pay type] = 'CP' THEN p.payment ELSE 0 END) AS CoPay,
    SUM(CASE WHEN p.[pay type] = 'MD' THEN p.payment ELSE 0 END) AS Medicare,    
    SUM(CASE WHEN p.[pay type] = 'IN' THEN p.payment ELSE 0 END) AS Insurance
FROM charges c
LEFT OUTER JOIN payments p ON
    p.[Patient ID] = c.[Patient ID] AND
    p.[Visit Number] = c.[Visit Number]
GROUP BY
    c.[Patient ID],
    c.[Visit Number],
    c.Charges

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Scott PletcherSenior DBACommented:
In that situation, my preference would still be to use MAX(c.Charges) to avoid another level of GROUPing.
0
rwheeler23Author Commented:
My apologies for being tardy with a response. I am waiting to get access back to the server where this data resides. As soon I can I will run these scripts on this data.
0
rwheeler23Author Commented:
Thanks to everyone for helping out here. Again I apologize for being so tardy with this. What could I have provided to have better illustrated this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.