Turnaround Times

Our practice managers wanted to be able to track the time from when a patient was checked in, to when they were roomed, to when they were seen by the provider, to when they were checked out. We spent some time researching how this could be done, and were not able to come up with a reliable way to identify rooming or when the doctor walked in.

We considered using the time vitals were taken as “roomed,” but the managers (who know their staff’s workflows far better than I) didn’t feel that would be accurate enough. And none of our providers start documenting as soon as they walk in the room, so we decided we had to abandon those two measures for now. But we can definitely track check-in to check-out.

I’m just going to cover the stored procedure for this. Once you have that, the report itself is a no-brainer. If you wanted to, you could probably pull this off in Crystal alone. However, in the event that we have an epiphany at some point in the future regarding those other milestones, it will be much easier to incorporate them into a stored procedure than an already-complicated report. So we might as well save ourselves some work.

It all comes down to finding two values in the ActivityLog table that show when the patient was arrived, and when the appointment was completed. This gives us our overall duration. So let’s start with a Common Table Expression:

One quick note before we dive in. You’ll notice I started this CTE with a semicolon, and you’ll see a lot of developers do this. You’ll find even more developers who will call you out for it. But there’s a very good reason for establishing this convention. If a CTE is not the first statement in a batch, the preceding statement must be terminated with a semicolon. Otherwise you’ll get an error. If you’re human like me, sometimes you forget your semicolons. So one day you decide you need to add a few lines of code above that CTE, and you forget to terminate. Thanks to the fact that you habitually add that semicolon, you don’t have to worry about throwing an error.

The purpose of this CTE is to give us the time the patient was arrived for each appointment within the date range. Do we really need to GROUP this? You would think not, as a patient can only arrive once. But go do a quick query to see if there are any appointments that were arrived multiple times:

I have 29,698 of them, how about you? So this is why we GROUP, and take the first instance as our arrival time.

Returning to our CTE, we’re starting with the Appointments table. In our WHERE clause we do some standard filtering to ensure we’re looking at non-canceled patient appointments within our date range. We link Appointments to ActivityLog to get our arrival time. We also link to a table variable called @Locations that lets us filter by practice. This is populated based on a parameter value – if you’ve read any of my previous posts you’re very familiar with this idea. If not, suffice to say that it’s a table variable that contains the LOCREG.FacilityId values of whatever practices we want to report on. You can exclude this if you don’t need to filter by location.

Also in our WHERE clause, we’re looking for ActivityLog records where TableName = ‘Appointments’ and Value2 = ‘Arrived’. We GROUP the whole thing by AppointmentsId and take the first arrival record we find. We now have a list of appointments and their arrival times.

There’s a comma at the end of that CTE because we’re going to follow it up with a second CTE:

This is practically the same as the cte_Arrivals. The differences are, instead of basing this CTE on Appointments, we base it on cte_Arrivals. We’ve already narrowed down our list of appointments, no sense in doing it twice. Just in case someone did something really stupid, we make sure the completed time is later than the arrival time. Finally, we’re taking the MAX() value this time, which is a somewhat arbitrary choice but it’s what we settled on.

Now we’re ready to return our results:

There should be nothing too confusing here. Because we’re INNER JOINing to our CTEs, we don’t need to filter the appointments in the WHERE clause. The rest of the table JOINs are just to pull relevant data. Finally, we were only interested in Office Visits, Office Procedures, Physicals, and Nurse Visits. So we filter on DOCTYPE to accomplish that.

That’s it, pretty simple. If you have any thoughts about how to pull the rooming time and provider arrival, I’d love to read about it in the comments!

One thought on “Turnaround Times

  1. Betsy Hail

    Hi, thank you for the information. In regards to additional tracking opportunities, I sat in on a CHUG a couple years ago where a group had created several “statuses” for their appointments. i.e. arrived, checked in, roomed by MA, physician in room etc. and ran reports based on this. I liked the concept, especially, because everyone could see where the patient was in their visit. They also used this data to calculate patient wait times for their appointment, another valuable measurement for patient satisfaction. But the deterrent for us was workflow, we just didn’t think that we could consistently get our staff, clinical and providers to reliably change the statuses for each of their patients. It may be easier if you are on the combined CPS product, but we still use the oracle EMR.

Leave a Reply