Appointment Times Scheduled After Cancellation
Back to Query Directory
I would like to know if a cancelled appointment time slot was later re-scheduled with an appointment that was kept.
The query below lists the original appointments that were cancelled, but subsequently replaced by one or more kept appointments during the same time period. You must, of course, replace the provided date range with one appropriate to your own needs. If you just want a count of how often this occurs, simply replace the first SELECT list with COUNT(*).
This query demonstrates the use of a correlated subquery in which the WHERE clause of the subquery uses conditions that reference the results of the outer query. Note that the rv_appts in the main query is given an alias, the letter "a". Using that letter in the subquery is what links it to values in the main result set.
SELECT
apptdate, apptstarttime, apptendtime, ptfullname, provcode
FROM
rv_appts a
WHERE
apptdate BETWEEN '2000-01-01' AND '2009-12-31'
AND cancelflag = 1
AND (SELECT count(*)
FROM rv_appts
WHERE apptdate = a.apptdate
AND providernum = a.providernum
AND apptstatus = 'AK'
AND apptstarttime BETWEEN a.apptstarttime AND a.apptendtime) > 0
apptdate, apptstarttime, apptendtime, ptfullname, provcode
FROM
rv_appts a
WHERE
apptdate BETWEEN '2000-01-01' AND '2009-12-31'
AND cancelflag = 1
AND (SELECT count(*)
FROM rv_appts
WHERE apptdate = a.apptdate
AND providernum = a.providernum
AND apptstatus = 'AK'
AND apptstarttime BETWEEN a.apptstarttime AND a.apptendtime) > 0
CategoryQueries
