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.
SELECT
a.firstname,
a.lastname,
(replace(a.phone1,'-','')) AS phnumber,
a.apptdate,
SUBSTR(CAST(a.apptstarttime AS CHAR),1,5) AS ApptTime
FROM
sos.rv_appts a
JOIN sos.patients c ON a.ptnum = c.ptnum
LEFT OUTER JOIN sos.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.
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
sos.appt_d a
JOIN sos.patients pt ON a.ptnum = pt.ptnum
JOIN sos.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 ''
;
Usage of this query for daily generation of the call list would usually mean setting it up to run automatically. To do so, you would create a Windows command file (batch file) in which you launch and run the query in the DBISQL utility, and use Windows task scheduler to run that command file on the desired days and times.
Let’s assume that you save the query in a file named APPT_REMINDERS.SQL, which you save in the SOS folder. Further, let’s assume that you have created a special user account REMIND, and assigned it the password “SECRET”. You would open Notepad or a similar plain text editor and create a file that you would name something like APPT_REMINDERS.CMD. The filename must have the extension CMD or BAT and you should save it in the SOS folder of the computer that will be used to send the data to your appointment reminder service vendor. In the file you would have a line similar to the one shown below, modified with the appropriate user id, password, and query filename. (TIP: The user ID must be all uppercase, and the case of the password must be exactly as originally created.)
c:\sos\sa\bin32\dbisql -c "uid=REMIND;pwd=SECRET;dsn=SOSDATA" C:\SOS\APPT_REMINDERS.SQL
The final step is to create a scheduled task in Windows to launch your CMD or BAT file on the days and times you like. That part depends on the version of Windows you are using, but it can be as simple as dragging your CMD file into the Scheduled Tasks folder to create a scheduled task (on Windows XP). You would then modify the properties of the task to reflect your preferences. In newer and server versions of Windows (Windows Vista, 7 and 8; Server 2008, 2008R2, 2012), you would open the Task Scheduler utility and create a “Basic Task”.
IMPORTANT: As described above, you end up with a user ID and password in a plain text file, sitting in the SOS folder. Arguably, that might be OK if the computer in question is a server that can be accessed only by IT staff who have access to everything anyway, but it would be an unacceptable security risk if implemented on a front office computer with questionable security. By definition, the account used to run this query can be used to query most everything in your database, so it must be protected. Here are some options:
- If running from the SOS folder of a server, make sure that the SOS folder of the server is NOT SHARED. There is no reason that it needs to be, even on a Terminal Server. For better protection against someone implementing a SHARE sometime in the future, use the next suggestion as well as placement on a protected server.
- Instead of storing the query and CMD file in the SOS folder, place them in a secured folder, only accessible by a system administrator. In that case, be sure to prefix all filenames in your command file with appropriate drive and path designations, and when you set up the scheduled task, be sure to configure it to run using an adminstrator account.
- For still better protection, contract with SOS to create a hidden user account on your system that has permission to run a stored-procedure version of your query, but has no other rights at all. That way, even if the ID and password of that account were to be compromised, all anyone could do with those credentials is to generate the next appointment call list and nothing else.
