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 sos.rv_appts a WHERE apptdate BETWEEN '2000-01-01' AND '2009-12-31' AND cancelflag = 1 AND (SELECT count(*) FROM sos.rv_appts WHERE apptdate = a.apptdate AND providernum = a.providernum AND apptstatus = 'AK' AND apptstarttime BETWEEN a.apptstarttime AND a.apptendtime) > 0