Revision [39]
This is an old revision of BillingAddressesForMailMerge made by admin on 2008-10-24 11:40:57.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