Felli Buds Felli Buds - 1 year ago 64
SQL Question

Adding year clause to count per patients appointments

In the following query, we count how many times the patient did not show for an appointment in the past. Now, we only want the counts for the current year. I am not succeeding in getting this part. I have put in

WHERE area WHERE year(vwgenpatapptinfo) = 2016
but it's not accepted.

SELECT Distinct vwGenPatApptInfo.Appt_DateTime, vwGenPatInfo.Patient_Name, vwGenPatApptInfo.Appt_Status, vwGenPatApptInfo.Appt_Sched_Department_ID, vwGenPatApptInfo.Appt_Sched_Department_Descr, vwGenPatApptInfo.Patient_id, vwGenPatApptInfo.Patient_number, vwGenPatApptInfo.Appt_NoShow_Date,
ISNULL(P.NotShowCount,0) AS NotShowCount
FROM vwGenPatInfo vwGenPatInfo INNER JOIN vwGenPatApptInfo vwGenPatApptInfo ON vwGenPatInfo.Patient_ID=vwGenPatApptInfo.Patient_ID
(SELECT Patient_ID, COUNT(Appt_Status) AS NotShowCount
FROM (SELECT Appt_DateTime, Appt_Status, Appt_Sched_Department_ID, Appt_Sched_Department_Descr, Appt_NoShow_Date, Patient_ID
FROM vwGenPatapptInfo AS vwGenPatApptInfo
WHERE (Appt_Status = 'N') AND (Appt_DateTime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), - 1))) AS L
GROUP BY Patient_ID) AS P ON vwGenPatInfo.Patient_ID=P.Patient_ID
WHERE vwGenPatApptInfo.Appt_Status='N'
ORDER BY vwGenPatApptInfo.Appt_Sched_Department_ID, vwGenPatApptInfo.Appt_DateTime

Answer Source

A) I think it needs to go into the inner WHERE - where it says WHERE (Appt_Status = 'N') AND (Appt_DateTime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), - 1))) AS L, inside the parenthesis; and
B) it should be year(vwgenpatapptinfo.appt_datetime) = 2016, not only year(vwgenpatapptinfo) = 2016.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download