SQL Matrix Table
Posted on 2015-01-21
I'm no SQL expert by any means but am trying to create a table with rows / columns based upon the following query. Basically I have a table of appointments with Appt_DateTime and Refer_Dr_Abbr ... I want to have column headings of each year and rows for each Referring Doctor. Each cell will contain the count of appointments for that year.
The examples I've seen are a bit over my head so I need some help on the easiest way to do this.
Thanks a million for any help you can give ....
SELECT Count(vwGenPatApptInfo.Appt_Refer_Dr_Abbr) AS Count,PM.vwRefDrList.Last_Name, OrgDesc
FROM vwGenPatApptInfo INNER JOIN
PM.vwRefDrList ON vwGenPatApptInfo.Appt_Refer_Dr_ID = PM.vwRefDrList.RefDrID
WHERE (vwGenPatApptInfo.Appt_DateTime BETWEEN CONVERT(DATETIME, '2010-01-01 00:00:01', 102) AND CONVERT(DATETIME, '2015-12-31 23:59:00', 102)) AND
(vwGenPatApptInfo.Appt_Status = 'A') AND OrgDesc IS NOT NULL --AND OrgDesc In ('Bend Memorial Clinic')
GROUP BY OrgDesc,vwGenPatApptInfo.Appt_Refer_Dr_Abbr, PM.vwRefDrList.Last_Name
Order BY OrgDesc, Count DESC