In the following, we get a count of how many times a patient did not show for an medical appointment (NOSHOW). It is based on if they did not show for the current day, then we display their count of this from the past. How can I get also the department that they did not show for? We have 6 different medical department so the manager wants to see if the problem is only for say dental, or for all. This will assist them from perhaps not booking someone etc.
ISNULL(P.NotShowCount,0) AS NotShowCount
vwGenPatInfo vwGenPatInfo j
INNER JOIN vwGenPatApptInfo vwGenPatApptInfo ON vwGenPatInfo.Patient_ID=vwGenPatApptInfo.Patient_ID
LEFT JOIN (
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
Patient_ID) AS P ON vwGenPatInfo.Patient_ID=P.Patient_ID
Patient_Name Appt_Sched_Departmen Appt_NoShow_Date Previous No Show Count
8/31/2016 No Shows 8/30/2016
Sinca Blay Adult Medicine 8/30/2016 12:05:46PM 12
Wiske Semns Adult Medicine 8/30/2016 5:25:32PM 4
Rose Alhar Adult Medicine 8/30/2016 5:57:01PM 6
Here is an untested query to give you an idea:
SELECT Patient_ID, Patient_Name, Appt_Sched_Department_ID, Appt_Sched_Department_Descr, Appt_NoShow_Date_Today, Appt_NoShow_Date_Prev, NotShowCount FROM (SELECT app.Patient_ID, pat.Patient_Name, app.Appt_Sched_Department_ID, app.Appt_Sched_Department_Descr, app.Appt_DateTime AS Appt_NoShow_Date_Today, LEAD(app.Appt_DateTime) OVER (PARTITION BY app.Patient_ID ORDER BY app.Appt_DateTime DESC) AS Appt_NoShow_Date_Prev, COUNT(app.Appt_Status) OVER (PARTITION BY app.Patient_ID) AS NotShowCount, ROW_NUMBER() OVER (PARTITION BY app.Patient_ID ORDER BY app.Appt_DateTime DESC) AS rn FROM vwGenPatApptInfo app INNER JOIN vwGenPatInfo pat ON pat.Patient_ID = app.Patient_ID WHERE app.Appt_Status = 'N' ) AS base WHERE rn = 1 AND Appt_NoShow_Date_Today >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) ORDER BY Appt_Sched_Department_ID, Appt_NoShow_Date_Today
The inner query gets all records with status 'N', but adds information:
Appt_DateTimevalue of the next record if the records are sorted by descending
Appt_DateTime(for the same patient: this is the "window").
NotShowCount: The number of records in that window.
rn: the sequential record number in that window, again when records are sorted by descending
The outer query only keeps the records with
rn = 1, which means: the record with the most recent
Appt_DateTime per patient. As a second condition, this
Appt_DateTime must be today (after last midnight): this makes sure we only list patients which were absent today.