SQL to determine which Practitioner a patient saw the most times.

select  distinct  A.Patient_id,   A.Appt_DateTime, P.Patient_name,  A.Patient_number,  P.Patient_Street1, P.Patient_DOB, P.Patient_Street2, P.Patient_City, P.Patient_State, v.service_date, P.Patient_Zip_Code, P.Patient_Home_Phone, D.Diagnosis_Code, 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     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 Left Join [PM].[Vouchers] V ON A.Patient_id = V.Patient_ID Inner Join [PM].[Services] S ON V.Voucher_ID = S.Voucher_ID Inner Join [PM].[Service_Diagnoses] SD ON S.Service_ID = SD.Service_ID Inner Join [PM].[Diagnosis_Codes] D ON SD.Diagnosis_Code_ID = D.Diagnosis_Code_ID where A.Appt_Cancelled_Date is NULL and A.Appt_Noshow_date is NULL and A.Appt_status = 'A'