CODEWITHSUNDEEP
sqlsql-server
Felli Buds
Felli Buds

Reputation: 31

Need to handle the rows that don't get picked

In the following, we are picking all patients who saw a Dr. but we remove 4 Doctors from this, because they are no longer in our facility. The problem here is that if a patient has ONLY seen from these 4 Doctors, they will not get selected. In fact, we want to assign them to b.Appt_resource_id = 142. How would i be able to also include such patients? The data we are getting from this sql looks like this:

    110811      737470                  Mirta  AVostaquishpe        19  

    102028      664770                  Brune  Alexgdre     19  

    70038       361830                  Pala  Avtila        19  

    44684       112740                  Nani  Verez     19  

    71939       382620                  Alex  Voachim       19

You can see that each patient is assigned to a Doctor via this b.Appt_Resource_ID. but this leaves out patients who have ONLY seen from the 4 Doctors who left the facility by now. How to included them and assign them to b.appt_resource_id= 142.

SELECT DISTINCT
    A.Patient_id,
    P.Patient_name,
    P.Patient_Last_Name,
    A.Patient_number,
    b.Appt_resource_id,
    b.appt_resource_descr 
FROM
    [PM].[vwGenPatApptInfo] A
INNER JOIN 
(
    SELECT TOP 100 PERCENT
        patient_id,
        Appt_resource_id,
        appt_resource_descr, 
        COUNT(Appt_resource_id) AS DR_count,
        ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY COUNT(*) DESC) AS seqnum
    FROM
        [PM].[vwGenPatApptInfo]
    WHERE
        Patient_ID IS NOT NULL AND
        Appt_Sched_Department_ID = 2 AND
        Appt_resource_id not IN (115, 123, 94, 109)
    GROUP BY
        patient_id,
        Appt_resource_id,
        appt_resource_descr
    ORDER BY
        patient_id,
        seqnum 
    ) B
    ON B.Patient_ID = A.Patient_ID  
    AND B.seqnum = 1 
INNER JOIN
    [PM].[vwGenPatInfo] P 
    ON A.Patient_id = P.Patient_id
WHERE
    A.Appt_Cancelled_Date IS NULL AND
    A.Appt_Noshow_date IS NULL

Upvotes: 0

Views: 44

Answers (2)

Matt
Matt

Reputation: 14341

seems like you are over complicating your query a lot. You actually do not want to limit your result set of your Appointments you just want to change the resource ID when it falls within certain values, thought I would question that too because you probably would like to know how many different doctors they saw. To do that you can use a case statement.

Here is a query to get the list of patients meeting the criteria counting the doctors (with the 4 changed to 1) and appointments. To get all of the appointment details would be a different type of query but doable so if that is what you want let us know.

SELECT
    p.Patient_id
    ,P.Patient_name
    ,P.Patient_Last_Name
    ,COUNT(DISTINCT CASE WHEN a.Appt_resource_id IN (115, 123, 94, 109) THEN 142 ELSE a.Appt_resource_id END) as DistinctDrCount
    ,COUNT(DISTINCT a.Appt_resource_id) as ActualUnAlteredDrCount
    ,COUNT(*) as NumOfAppointments
FROM
    [vwGenPatApptInfo] a
    INNER JOIN [PM].[vwGenPatInfo] P 
    ON A.Patient_id = P.Patient_id
WHERE
    A.Appt_Cancelled_Date IS NULL AND
    A.Appt_Noshow_date IS NULL
GROUP BY
    p.Patient_id
    ,P.Patient_name
    ,P.Patient_Last_Name

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

Take out the line where you filter out the four doctors, and use this line in your main SELECT instead:

CASE 
   WHEN b.Appt_resource_id IN (115, 123, 94, 109) THEN 142 
   ELSE b.Appt_resource_id 
END AS Appt_resource_id

Upvotes: 0

Related Questions