sql syntax case statement
Posted on 2014-08-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
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.