Minimizing the time patients wait to be seen is a critical component of their overall satisfaction. Those who wait more than 20 minutes tend to leave unsatisfied, per Dentistry Today. The inverse is also true. Dentists who nail a five-star rating average a 13-minute wait. But how can you tell how long your patients are waiting? Practice-Web has an easy answer: Queries. Queries allow you to generate custom reports by extracting virtually any data you wish from your software.
How to Run Queries in Practice-Web
To run a query, you’ll simply go to Main Menu, then click Reports, and select User Query. You’ll see in the image below there’s a sample query that begins with /*370. This is the text box where you’ll add the query given in the next step. In this case, it’s a simple copy/paste from this article into Practice-Web, but you can create your own custom queries too.
Detailed Waiting Room Report Query
The report below will give you totals and averages, procedure, and appointment information. It shows averages and totals for wait time, chair time, appointment length and filters by clinic. Note the orange from and to dates. You’ll want to adjust these to fit your preferred reporting period.
/*Detailed Waiting Room Report with totals and averages, procedure and appointment information*/
SET @FromDate='2021-01-01' , @ToDate='2021-06-30';
SET @Clinics=''; -- Clinic abbrevations separated by a pipe | symbol, 'None' for appointments with no clinic, leave blank to show all
/* ------------ DO NOT MODIFY BELOW THIS LINE ------------ */
#
SET @Clinics=(CASE WHEN LENGTH(@Clinics)=0 THEN '^' ELSE CONCAT('^',REPLACE(@Clinics,'|','$|^'),'$') END);
#
SET @WaitTimeSec=0,@WaitTimeCnt=0,@AptTimeSec=0,@AptTimeCnt=0,@ChairTimeSec=0,@ChairTimeCnt=0; -- Totals row variables
SELECT
apts.PatientName,
apts.PatNum AS 'Pat Num',
apts.AptDateTime,
apts.ProvNum,
apts.Assistant,
apts.AppointmentTypeName,
apts.Op,
apts.Clinic,
apts.TimeArrived,
apts.TimeSeated,
apts.TimeDismiss,
apts.WaitTime,
apts.ChairTime,
apts.AptLength,
apts.ProcDescript
FROM (
SELECT
CONCAT( -- Build a name string like OD's default
TRIM(p.LName),
',',
(CASE WHEN LENGTH(TRIM(p.Preferred)) > 0 THEN CONCAT(' \'', TRIM(p.Preferred),'\'') ELSE '' END), -- Display preferred if we have one
' ',
TRIM(p.FName),
(CASE WHEN LENGTH(TRIM(p.MiddleI)) > 0 THEN CONCAT(' ', TRIM(p.MiddleI)) ELSE '' END) -- Display middle initial if we have one
) AS 'PatientName',
LEFT(a.PatNum,15) AS 'PatNum',
a.AptDateTime,
a.ProvNum,
a.Op,
COALESCE(cl.Abbr, 'None') AS 'Clinic',
(CASE
WHEN DATE_FORMAT(DateTimeArrived,'%T')<>'00:00:00'/*when there is a real value*/
THEN DATE_FORMAT(DateTimeArrived,'%r')
ELSE ''
END) AS 'TimeArrived',
(CASE
WHEN DATE_FORMAT(DateTimeSeated,'%T')<>'00:00:00'/*when there is a real value*/
THEN DATE_FORMAT(DateTimeSeated,'%r')
ELSE ''
END) AS 'TimeSeated',
(CASE
WHEN DATE_FORMAT(DateTimeDismissed,'%T')<>'00:00:00'/*when there is a real value*/
THEN DATE_FORMAT(DateTimeDismissed,'%r')
ELSE ''
END) AS 'TimeDismiss',
(CASE
WHEN DATE_FORMAT(DateTimeSeated,'%T')<>'00:00:00' AND DATE_FORMAT(DateTimeArrived,'%T')<>'00:00:00'
THEN TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(DateTimeSeated)-TIME_TO_SEC(DateTimeArrived)),'%H:%i')
ELSE ''
END) AS 'WaitTime',
(CASE
WHEN DATE_FORMAT(DateTimeSeated,'%T')<>'00:00:00' AND DATE_FORMAT(DateTimeArrived,'%T')<>'00:00:00'
THEN CONCAT(
@WaitTimeSec := @WaitTimeSec + TIME_TO_SEC(TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(DateTimeSeated)-TIME_TO_SEC(DateTimeArrived)),'%H:%i')),
@WaitTimeCnt := @WaitTimeCnt + 1
)
ELSE ''
END) AS 'WaitTimeT',
(CASE
WHEN DATE_FORMAT(DateTimeSeated,'%T')<>'00:00:00' AND DATE_FORMAT(DateTimeDismissed,'%T')<>'00:00:00'
THEN TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(DateTimeDismissed)-TIME_TO_SEC(DateTimeSeated)),'%H:%i')
ELSE ''
END) AS 'ChairTime',
(CASE
WHEN DATE_FORMAT(DateTimeSeated,'%T')<>'00:00:00' AND DATE_FORMAT(DateTimeDismissed,'%T')<>'00:00:00'
THEN CONCAT(
@ChairTimeSec := @ChairTimeSec + TIME_TO_SEC(TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(DateTimeDismissed)-TIME_TO_SEC(DateTimeSeated)),'%H:%i')),
@ChairTimeCnt := @ChairTimeCnt + 1
)
ELSE ''
END) AS 'ChairTimeT',
(CASE
WHEN DATE_FORMAT(DateTimeDismissed,'%T')<>'00:00:00' AND DATE_FORMAT(DateTimeArrived,'%T')<>'00:00:00'
THEN TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(DateTimeDismissed)-TIME_TO_SEC(DateTimeArrived)),'%H:%i')
ELSE ''
END) AS 'AptLength',
(CASE
WHEN DATE_FORMAT(DateTimeDismissed,'%T')<>'00:00:00' AND DATE_FORMAT(DateTimeArrived,'%T')<>'00:00:00'
THEN CONCAT(
@AptTimeSec := @AptTimeSec + TIME_TO_SEC(TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(DateTimeDismissed)-TIME_TO_SEC(DateTimeArrived)),'%H:%i')),
@AptTimeCnt := @AptTimeCnt + 1
)
ELSE ''
END) AS 'AptLengthT',
a.ProcDescript,
aptt.AppointmentTypeName,
CONCAT(emp.FName, ' ', emp.LName) AS 'Assistant'
FROM appointment a
INNER JOIN patient p
ON a.PatNum=p.PatNum
LEFT JOIN clinic cl
ON a.ClinicNum = cl.ClinicNum
LEFT JOIN employee emp
ON a.Assistant = emp.EmployeeNum
LEFT JOIN appointmenttype aptt
ON a.AppointmentTypeNum = aptt.AppointmentTypeNum
WHERE a.AptDateTime BETWEEN @FromDate AND @ToDate + INTERVAL 1 DAY
AND a.AptStatus = 2
AND COALESCE(cl.Abbr, 'None') REGEXP @Clinics
ORDER BY a.AptDateTime
) apts
UNION ALL
SELECT '--------------','','','','','','','','','','','-------','','',''
UNION ALL
SELECT 'Average','','','','','','','','','','',CONCAT(FORMAT(((@WaitTimeSec/@WaitTimeCnt)/60),1),' Minutes'),CONCAT(FORMAT(((@ChairTimeSec/@ChairTimeCnt)/60),1),' Minutes'),CONCAT(FORMAT(((@AptTimeSec/@AptTimeCnt)/60),1),' Minutes'),''
UNION ALL
SELECT '--------------','','','','','','','','','','','-------','','',''
UNION ALL
SELECT 'Totals','','','','','','','','','','',CONCAT(FORMAT((@WaitTimeSec/60),1),' Minutes'),CONCAT(FORMAT((@ChairTimeSec/60),1),' Minutes'),CONCAT(FORMAT((@AptTimeSec/60),1),' Minutes'),''Hit the User’s Guide if you’d like to learn more about Queries or the Waiting Room feature. You can also get in touch if you’d like help running this report. Assistance is free for practices with paid Support. If your office does not have active Support, let us know and we’ll help you get set up.