Frank Budapest Frank Budapest - 6 months ago 10
SQL Question

Query returns incorrect result

MY input table is patient_ID, and Appt_Resource_ID (Doctor) ( the second table is just getting the patient name )

Patient_ID Appt_Resource_ID
88299 47
88299 1
88299 40
88299 40
88299 40
88299 40


I am running an sql that is meant to write an output row for the patient_id and the Doctor_ID that occurs the most, in this case sb 40. But it's outputting Doctor_ID 1. Other cases I checked are doing correctly.

This is the query:

select distinct A.Patient_id, P.Patient_name, b.Appt_resource_id
from [PM].[vwGenPatApptInfo] A
inner join
(
select top 100 percent patient_id, Appt_resource_id, 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
group by patient_id,Appt_resource_id
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_DateTime >= DATEADD(yyyy, -2, GETDATE()) and A.Appt_Cancelled_Date is NULL


But the results are this:

Patient_ID Appt_Resource_ID
88299 1

Answer

Try this.

SELECT TOP 1 *
FROM (
    SELECT Patient_ID, Appt_Resource_ID, COUNT(*) AS theCount
    FROM vwGenPatApptInfo
    GROUP BY Patient_ID, Appt_Resource_ID
    WHERE ApptDateTime >= DATEADD(yyyy, -2, GETDATE()) and Appt_Cancelled_Date is NULL
) b
JOIN vwGenPatInfo p ON p.Patient_ID = b.Patient_ID
ORDER BY theCount DESC