Fat Budapest Fat Budapest - 1 year ago 73
SQL Question

Need to select ONLY if visiting one location

In the following SQL, it was looking at only those vouchers from location_ID = 5. How can I code that, I ONLY want from a Patient_ID who ONLY visited location_ID 5?

SELECT "Vouchers"."Patient_ID", "vwGenPatInfo"."Patient_Number",
"Practitioners"."Practitioner_ID", "Practitioners"."First_Name",
"Practitioners"."Last_Name", "vwGenPatInfo"."Patient_First_Name",
"vwGenPatInfo"."Patient_Last_Name", "vwGenPatInfo"."Patient_DOB",
"vwGenPatInfo"."Patient_Sex", "Vouchers"."Carrier_ID",
"Vouchers"."Billing_Date", "Vouchers"."Patient_Policy_ID",
FROM ("Ntier_70751"."PM"."vwGenPatInfo" "vwGenPatInfo"
INNER JOIN "Ntier_70751"."PM"."Vouchers" "Vouchers"
ON "vwGenPatInfo"."Account_ID"="Vouchers"."Account_ID")
INNER JOIN "Ntier_70751"."PM"."Practitioners" "Practitioners"
ON "Vouchers"."Actual_Prov_Practitioner_ID"="Practitioners"."Practitioner_ID"
WHERE "Vouchers"."Location_ID"=5

Answer Source

Here is one way to do this. I also got rid of all those unneeded double quotes and used proper aliases.

    , gpi.Patient_Number
    , P.Practitioner_ID
    , P.First_Name
    , P.Last_Name
    , gpi.Patient_First_Name
    , gpi.Patient_Last_Name
    , gpi.Patient_DOB
    , gpi.Patient_Sex
    , V.Carrier_ID
    , V.Billing_Date
    , V.Patient_Policy_ID
    , V.Location_ID
FROM Ntier_70751.PM.vwGenPatInfo gpi 
INNER JOIN Ntier_70751.PM.Vouchers V ON gpi.Account_ID = V.Account_ID 
INNER JOIN Ntier_70751.PM.Practitioners P ON V.Actual_Prov_Practitioner_ID = P.Practitioner_ID
cross apply
    select V2.Account_ID
    from Ntier_70751.PM.Vouchers V2
    where V2.Account_ID = V.Account_ID

    group by V2.Account_ID
    HAVING MAX(Location_ID) = 5
        AND MIN(Location_ID) = 5
) x
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download