I want to print sets of patient mailing labels, but filtering for specified primary provider codes, patient category, and patient age. Include only patients who have been seen for a chargeable service within the last year.
One effective way to do this is to create a result set that matches what Microsoft Word expects in a mailing list and export it to Excel format. You can then use the Mail Merge wizard in Word to very easily create your labels. Note that we have used the TRIM function on some of the elements to be sure that any extra spaces are removed from the end of the data. We have also used AS to rename the data elements to match what Word is looking for. That saves the step of matching fields when setting up your Mail Merge. This query uses a custom function “AgeInYears” that SOS provides in your database so that you can get accurate age calculations by simply providing the date of birth and the target date. Here we are interested in the patient’s age right now, so instead of hard-coding a date, we use the SQL function TODAY(), which is replaced automatically by the current date when we run the query. The same TODAY() function is used in the condition that restricts the patients to those seen in the past year.
For a video that shows how to do MS Word mail-merge labels using the Excel file produced by this query, go to…
SELECT TRIM(a.firstname) AS "First Name", TRIM(a.lastname) AS "Last Name", a.addr1 AS "Address 1", a.addr2 AS "Address 2", TRIM(city) AS "City", TRIM(state) AS "State", zip FROM sos.rv_patients a JOIN sos.ptvars b ON a.ptnum = b.ptnum WHERE a.licnum = 101 AND a.priprvcode IN ('AF','AFB') AND a.categcode = 'C' AND sos.AgeInYears(a.dob,TODAY() ) BETWEEN 0 AND 80 AND b.lfeedate > (TODAY() - 365) ORDER BY a.lastname, a.firstname ; OUTPUT TO c:\sos\labels.html FORMAT HTML