Billing Addresses For Mail Merge
Back to Query Directory
The following query will create an Excel file suitable for Mail Merge use. The results will have information from the "Bill To" tab of patient information, if present, otherwise will use the patient information. It does not pull information from alternate payors on the account.
*Note: You must use DBISQLG in order to output in Excel format.
SELECT
TRIM(IF a.billtofirstname > '' THEN a.billtofirstname ELSE a.firstname ENDIF) AS "FirstName",
TRIM(IF a.billtolastname > '' THEN a.billtolastname ELSE a.lastname ENDIF) AS "LastName",
TRIM("firstname" + ' ' + "lastname") AS "FullName",
TRIM(IF a.billtoaddr1 > '' THEN a.billtoaddr1 ELSE b.Addr1 ENDIF) AS "AddressLine1",
TRIM(IF a.billtoaddr2 > '' THEN a.billtoaddr2 ELSE b.Addr2 ENDIF) AS "AddressLine2",
TRIM(IF a.billtocity > '' THEN a.billtocity ELSE b.City ENDIF) AS "City",
TRIM(IF a.billtostate > '' THEN a.billtostate ELSE b.State ENDIF) AS "State",
TRIM(IF a.billtozip > '' THEN a.billtozip ELSE b.Zip ENDIF) AS "Zip"
FROM
patients a JOIN payors b ON a.payornum = b.payornum
WHERE
a.flag = 0 /* only patients in active list */
AND a.licnum = 101 /*only patients in the main data set*/
;
OUTPUT TO c:\sos\billingaddresses.xls FORMAT EXCEL
TRIM(IF a.billtofirstname > '' THEN a.billtofirstname ELSE a.firstname ENDIF) AS "FirstName",
TRIM(IF a.billtolastname > '' THEN a.billtolastname ELSE a.lastname ENDIF) AS "LastName",
TRIM("firstname" + ' ' + "lastname") AS "FullName",
TRIM(IF a.billtoaddr1 > '' THEN a.billtoaddr1 ELSE b.Addr1 ENDIF) AS "AddressLine1",
TRIM(IF a.billtoaddr2 > '' THEN a.billtoaddr2 ELSE b.Addr2 ENDIF) AS "AddressLine2",
TRIM(IF a.billtocity > '' THEN a.billtocity ELSE b.City ENDIF) AS "City",
TRIM(IF a.billtostate > '' THEN a.billtostate ELSE b.State ENDIF) AS "State",
TRIM(IF a.billtozip > '' THEN a.billtozip ELSE b.Zip ENDIF) AS "Zip"
FROM
patients a JOIN payors b ON a.payornum = b.payornum
WHERE
a.flag = 0 /* only patients in active list */
AND a.licnum = 101 /*only patients in the main data set*/
;
OUTPUT TO c:\sos\billingaddresses.xls FORMAT EXCEL
Let's say that you just want addresses for patients who have been seen for billable services in the past two years (730 days). There is a column in the ptvars view called "lfeedate" that has the date of the last billable service. So we can add ptvars to the FROM clause, and the lfeedate to the WHERE clause, as shown below:
SELECT
TRIM(IF a.billtofirstname > '' THEN a.billtofirstname ELSE a.firstname ENDIF) AS "FirstName",
TRIM(IF a.billtolastname > '' THEN a.billtolastname ELSE a.lastname ENDIF) AS "LastName",
TRIM("firstname" + ' ' + "lastname") AS "FullName",
TRIM(IF a.billtoaddr1 > '' THEN a.billtoaddr1 ELSE b.Addr1 ENDIF) AS "AddressLine1",
TRIM(IF a.billtoaddr2 > '' THEN a.billtoaddr2 ELSE b.Addr2 ENDIF) AS "AddressLine2",
TRIM(IF a.billtocity > '' THEN a.billtocity ELSE b.City ENDIF) AS "City",
TRIM(IF a.billtostate > '' THEN a.billtostate ELSE b.State ENDIF) AS "State",
TRIM(IF a.billtozip > '' THEN a.billtozip ELSE b.Zip ENDIF) AS "Zip"
FROM
patients a
JOIN payors b ON a.payornum = b.payornum
JOIN ptvars c ON a.ptnum = c.ptnum
WHERE
a.flag = 0 /* only patients in active list */
AND a.licnum = 101 /*only patients in the main data set*/
AND c.lfeedate > (CURRENT DATE - 730) /* service within past two years */
;
OUTPUT TO c:\sos\billingaddresses.xls FORMAT EXCEL
TRIM(IF a.billtofirstname > '' THEN a.billtofirstname ELSE a.firstname ENDIF) AS "FirstName",
TRIM(IF a.billtolastname > '' THEN a.billtolastname ELSE a.lastname ENDIF) AS "LastName",
TRIM("firstname" + ' ' + "lastname") AS "FullName",
TRIM(IF a.billtoaddr1 > '' THEN a.billtoaddr1 ELSE b.Addr1 ENDIF) AS "AddressLine1",
TRIM(IF a.billtoaddr2 > '' THEN a.billtoaddr2 ELSE b.Addr2 ENDIF) AS "AddressLine2",
TRIM(IF a.billtocity > '' THEN a.billtocity ELSE b.City ENDIF) AS "City",
TRIM(IF a.billtostate > '' THEN a.billtostate ELSE b.State ENDIF) AS "State",
TRIM(IF a.billtozip > '' THEN a.billtozip ELSE b.Zip ENDIF) AS "Zip"
FROM
patients a
JOIN payors b ON a.payornum = b.payornum
JOIN ptvars c ON a.ptnum = c.ptnum
WHERE
a.flag = 0 /* only patients in active list */
AND a.licnum = 101 /*only patients in the main data set*/
AND c.lfeedate > (CURRENT DATE - 730) /* service within past two years */
;
OUTPUT TO c:\sos\billingaddresses.xls FORMAT EXCEL
CategoryQueries
