No-shows and late cancels are a significant cause of revenue loss for a practice. There’s little we can do from a reporting perspective to prevent these events, but today we’ll look at a report that at least allows the practices to track them.
Note, this report was requested by one of our practices, and they wanted the data presented in a manner that I personally find a bit clumsy. I’m going to present it here the way I designed it for them, but I’ll insert my own thoughts about the optimal presentation method here and there.
The specification was to show each day of the previous month (excluding dates where no appointments were scheduled), with a detailed list of all no-shows and late cancels, a total count of each, and the total appointments scheduled and patients seen that day. Totals for the month would appear at the bottom of the report.
Grouping the report by day struck me as an odd choice, and would certainly present a challenge in terms of presentation and readability. But this is what they wanted.
I decided a stored procedure was a better choice than trying to do this in Crystal, primarily because I was going to have to show dates with no data. Crystal is not good at reporting on the absence of data, generally speaking. Here is cus_RPT_NoShowsAndLateCancels…
CREATE PROCEDURE dbo.cus_RPT_NoShowsAndLateCancels
SET NOCOUNT ON;
DECLARE @StartDate datetime,
SELECT @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0), --First day of previous month
@EndDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1), --Last Day of previous month
@FacilityId = FacilityId
WHERE ABBREVNAME = @LocationString;
--Date tally table so we can include all dates
--regardless of whether or not there are no-shows or late cancels
DECLARE @DateTable table (ApptDate datetime PRIMARY KEY, ApptCount int, SeenCount int);
SET @d = @StartDate;
WHILE @d <= @EndDate
INSERT INTO @DateTable
VALUES (@d, 0, 0);
SET @d = DATEADD(day,1,@d);
SET ApptCount = sub.ApptCount,
SeenCount = sub.SeenCount
FROM @DateTable d
INNER JOIN (SELECT d.ApptDate,
COUNT(DISTINCT a.AppointmentsId) AS ApptCount,
SUM(CASE WHEN ISNULL(a.Canceled,0) = 0
AND NOT a.ApptStatusMId IN (157313,1841,309)
THEN 1 ELSE 0 END) AS SeenCount
FROM @DateTable d
LEFT JOIN Appointments a
ON d.ApptDate = a.EmrApptStart
WHERE a.FacilityId = @FacilityId
AND a.ApptKind = 1
GROUP BY d.ApptDate) sub
ON d.ApptDate = sub.ApptDate;
WITH cte_NoShows AS
( SELECT p.PatientId,
p.Last + ', ' + p.First + ISNULL(' ' + p.Middle, '') AS Patient,
p.Birthdate AS DOB,
a.EmrApptStart AS ApptDate,
a.ApptStart AS ApptDateTime,
m.Description + 's' AS ApptStatus
FROM Appointments a
INNER JOIN PatientProfile p
ON a.OwnerId = p.PatientProfileId
AND a.ApptKind = 1
INNER JOIN MedLists m
ON a.ApptStatusMId = m.MedListsId
WHERE a.ApptStatusMId IN (157313,1841,309)
AND a.EmrApptStart BETWEEN @StartDate AND @EndDate
AND a.FacilityId = @FacilityId
SELECT d.ApptDate AS GroupDate,
d.ApptCount AS [Scheduled Appts],
d.SeenCount AS [Patients Seen],
ISNULL(c.Patient, 'No no-shows or late cancels found') AS Patient,
ISNULL(c.ApptStatus,'') AS [Appt Status],
c.ApptDateTime AS [Appt Date/Time]
FROM @DateTable d
LEFT JOIN cte_NoShows c
ON d.ApptDate = c.ApptDate
WHERE d.ApptCount > 0
In many previous posts, my @LocationString parameter is a pipe-separated list of LOCREG.ABBREVNAME values, which are parsed into a table variable via a simple string-parser function. I saw no need to make this a multi-location report, so I’ve excluded that here.
In lines 7-15 I’m setting up my variables, getting the first and last day of the previous month, and the LOCREG.FacilityId value of the location I’m reporting on.
In lines 19-26, I’m setting up a tally table to contain all the dates in that month. I will need this in order to report on any dates where there are no no-shows or late cancels.
Please note, there are more efficient ways of creating a tally table full of dates than a loop. You can find any number of examples on the Internet. However, since we’re only talking about 31 records at most, there will be no measurable difference between this and other methods. Plus, this method is going to be more easily understandable to anyone reviewing this code in the future.
The next block on lines 28-43 updates @DateTable with the total number of scheduled appointments and patients seen for each date. We use a combination of the Canceled field and ApptStatusMId to determine whether the patient was actually seen.
On lines 45-60 we build cte_NoShows, a common table expression that contains all no-shows and late cancels for the month. Note that I’m tacking an “s” on to the end of the appointment status description. I’m going to use this for grouping in the report, so for example the status “Late cancel” will become “Late cancels”. I could also have tacked this on in the group name formula of the report, I just felt like doing it here.
On lines 62-73 I LEFT JOIN our date tally table to the CTE. I have to do a LEFT JOIN, remember, because I need all of my dates regardless of whether there are any cancels or no-shows in the CTE. In the event there are none, I’ll get a single record for that date with a blank status and “No no-shows or late cancels found” in the patient name. I’m excluding any appointment-free days in the WHERE clause.
So the procedure is really pretty simple. Figuring out how best to present it in the report was trickier, given the specification. I’ve attached the report right here so you can take a look at it in Crystal if you like, but here’s a screen shot for quick reference:
I admit it’s a bit ugly, but I needed each date group to parse visually, so a box with a drop shadow seemed like my best bet.
Figuring out how to include the appointments/patients seen totals on each date group was tricky, too. Running it across the top of the group just didn’t work. So I ran them down the left side of the report in the Group Header 1 section (grouping on date), and set the section to underlay following sections. This placed those totals right alongside the main data.
Note, using non-aggregated data fields in the Group Header like this is not exactly best practice. I’m only getting away with it here because I know those totals are going to be the same for every record in the group, and I couldn’t come up with a more practical way of doing it.
Group 2 is grouping by status, and listing the appointments. I suppressed Group Header 2 if the status came back blank. This tells me there are no no-shows or late cancels for that date, so all I want to show is the one record that says that in the Patient field.
In the Report Footer of the subreport, the Scheduled and Seen totals are running total fields that evaluate on a change of the date group. It was necessary to do it this way rather than a straight summary field because of the fact that I included these values in every record. (Remember what I said about this not exactly being best practice?)
The no-shows and late cancels totals are also running total fields, but they are counts that evaluate based on appointment status.
Given my druthers, I wouldn’t try to group this by date, it’s just too awkward. I’d rather set up the report for export to Excel and allow the end user to pivot the data as they see fit. The script as it stands can certainly be repurposed for that layout, though the report would obviously need a complete overhaul.