?
Solved

sql syntax case statement

Posted on 2014-08-26
6
Medium Priority
?
304 Views
Last Modified: 2014-09-26
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.
0
Comment
Question by:rwheeler23
6 Comments
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 40286244
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40286308
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40287072
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40288022
In that situation, my preference would still be to use MAX(c.Charges) to avoid another level of GROUPing.
0
 

Author Comment

by:rwheeler23
ID: 40293248
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
 

Author Closing Comment

by:rwheeler23
ID: 40347197
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Loops Section Overview
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question