May 18, 2012 at 11:13 am
I have two tables: a Patient Table and a charge detail table. I would like to have one row per patient and for the query to add charge detail by department. So...would like it to look like this:
PatientID/DCDate/PatientType/ED(sumofcharges)/Lab(sumofcharges)/Pediatrics(sumofcharges)
in the department field there will be 2 or more department codes that roll up into one..for example: 111 and 112 are both ED charges, 113 and 114 are lab charges. So I'd like those to be rolled up into one ED Charge for each patient and put into one "ED" column, and both lab charges to be rolled into one "Lab" column, etc . Hope that makes sense. Thanks for looking.
Patient tablePatientID
DCDate
Patienttype
Charge Detail tablePatientID
DcDate
Department
Chargecode
Charge
May 18, 2012 at 11:28 am
looks like a sum of case statements to me:
you have to group by the patient,and then selectively sum the charges based on the Department(code?)
something like this, i would think:
SELECT
P.tablePatientID,
SUM(CASE WHEN Department IN(111,112) THEN Charge ELSE 0 END) AS EDCharges,
SUM(CASE WHEN Department IN(113,114) THEN Charge ELSE 0 END) AS LabCharges,
SUM(CASE WHEN Department NOT IN(111,112,113,114) THEN Charge ELSE 0 END) AS OtherCharges
FROM Patient P
LEFT OUTER JOIN [Charge Detail] D
ON P.tablePatientID=D.tablePatientID
GROUP BY tablePatientID
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply