Appointment Times Scheduled After Cancellation

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

 

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.