Mailing Labels by Primary Provider, Pt Category, and Age

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…
http://www.sosoft.com/files/tv/other/querylabelmerge.swf

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

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.