Revision [205]
Last edited on 2009-01-07 15:07:03 by SethKriegerAdditions:
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]]
[[http://www.sosoft.com/files/tv/other/querylabelmerge.swf]]
Revision [204]
Edited on 2009-01-07 11:56:42 by SethKriegerAdditions:
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.
AND a.priprvcode IN ('AF','AFB')
AND b.lfeedate > (TODAY() - 365)
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.
AND a.priprvcode IN ('AF','AFB')
AND b.lfeedate > (TODAY() - 365)
Deletions:
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.
AND a.priprvcode = '6'
Revision [203]
Edited on 2009-01-06 15:40:33 by SethKriegerAdditions:
======Mailing Labels by Primary Provider, Pt Category, and Age======
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
%%
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
%%
Deletions:
comments here appear in italics
paste your tested query here
%%
