Revision [203]
This is an old revision of MailingLabelsByProviderCategoryAge made by SethKrieger on 2009-01-06 15:40:33.Mailing Labels by Primary Provider, Pt Category, and Age
Back to Query Directory
I want to print sets of patient mailing labels, but filtering for specified primary provider, patient category, and patient age.
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.
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
rv_patients a
JOIN ptvars b ON a.ptnum = b.ptnum
WHERE
a.licnum = 101
AND a.priprvcode = '6'
AND a.categcode = 'C'
AND AgeInYears(a.dob,TODAY() ) BETWEEN 0 AND 80
ORDER BY
a.lastname, a.firstname
;
OUTPUT TO c:\sos\labels.xls FORMAT EXCEL
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
rv_patients a
JOIN ptvars b ON a.ptnum = b.ptnum
WHERE
a.licnum = 101
AND a.priprvcode = '6'
AND a.categcode = 'C'
AND AgeInYears(a.dob,TODAY() ) BETWEEN 0 AND 80
ORDER BY
a.lastname, a.firstname
;
OUTPUT TO c:\sos\labels.xls FORMAT EXCEL
CategoryQueries
