The following query was done for a user who wanted to automatically export names, phone numbers, and appointment dates and times for uploading to Televox (www.televox.com) for automated appointment reminder calls. A call will be made two days before the appointment, and the day before.
In this query, UserDefined field one on the patient form is checked for a “N”. This field should be set up as “OK for auto appt reminders”. If you enter an “N” in that field the patient will not be included in the calls.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | SELECT a.firstname, a.lastname, (REPLACE(a.phone1,'-','')) AS phnumber, a.apptdate, SUBSTR(CAST(a.apptstarttime AS CHAR),1,5) AS ApptTime FROM rv_appts a JOIN patients c ON a.ptnum = c.ptnum LEFT OUTER JOIN uddatapt d ON c.uddatanum = d.uddatanum WHERE // exclude cancellations cancelflag = 0 // ONLY IF phone NUMBER IS at least 7 digits AND LENGTH(TRIM(phone1)) > 7 //appts FOR tomorrow OR NEXT DAY AND (apptdate = dateadd(DAY,1,CURRENT DATE) OR apptdate = dateadd(DAY,2,CURRENT DATE)) // UD FIELD IS ok FOR phone reminders AND (d.fld1 <> 'N' OR d.fld1 IS NULL) ; // SET file FOR output OUTPUT TO \sos\phone-reminder.txt FORMAT ASCII QUOTE '' ; |
Here is a variation of the above. In this case, a phone type of PREF is used for all those accounts that you want to be included in the reminder system. If an account has just HOME and WORK phones, for example, no reminder call will be made. The account is queued for a call just once, two days before the appointment.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | SELECT a.firstname, a.lastname, (CASE WHEN pay.phone1desc = 'PREF' THEN (REPLACE(pay.phone1area+pay.phone1,'-','')) WHEN pay.phone2desc = 'PREF' THEN (REPLACE(pay.phone2area+pay.phone2,'-','')) WHEN pay.phone3desc = 'PREF' THEN (REPLACE(pay.phone3area+pay.phone3,'-','')) ELSE '' END CASE) AS prefphonenumber, a.adate, SUBSTR(CAST(a.stime AS CHAR),1,5) AS ApptTime FROM appt_d a JOIN patients pt ON a.ptnum = pt.ptnum JOIN payors pay ON pt.payornum = pay.payornum WHERE // exclude cancellations cancelflag = 0 // ONLY IF PREF phone NUMBER at least 7 digits AND LENGTH(TRIM(prefphonenumber)) > 7 //appts FOR DAY after tomorrow AND a.adate = dateadd(DAY,2,CURRENT DATE) ; // SET file FOR output OUTPUT TO \sos\phone-reminder.txt FORMAT ASCII QUOTE '' ; |