Post reply

subqueries

  • May 18, 2012 at 11:13 am

    #256657

    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

    #1489858

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply