Billing Addresses For Mail Merge

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. The file generated is in HTML format, but you can open it in Excel and re-save as an Excel format file.

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
  sos.patients a JOIN sos.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.html FORMAT HTML

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
  sos.patients a
  JOIN sos.payors b ON a.payornum = b.payornum
  JOIN sos.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.html FORMAT HTML