SOS Query Wiki: Billing Addresses For Mail Merge

Revision [426]

Last edited on 2010-10-05 10:08:44 by SethKrieger

Additions:

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:
patients a
JOIN payors b ON a.payornum = b.payornum
JOIN ptvars c ON a.ptnum = c.ptnum
AND c.lfeedate > (CURRENT DATE - 730) /* service within past two years */

Revision [102]

Edited on 2008-10-26 16:49:30 by admin

Additions:

----
CategoryQueries

Revision [92]

Edited on 2008-10-26 16:13:44 by admin

Additions:

a.flag = 0 /* only patients in active list */
AND a.licnum = 101 /*only patients in the main data set*/

Deletions:

a.flag = 0 // only patients in active list
AND a.licnum = 101 //only patients in the main data set

Revision [91]

Edited on 2008-10-26 16:12:24 by admin

Additions:

%%(sql)
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"
patients a JOIN payors b ON a.payornum = b.payornum
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
%%

Deletions:

%%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"
patients a JOIN payors b ON a.payornum = b.payornum
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%%

Revision [39]

Edited on 2008-10-24 11:40:57 by admin

Additions:

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",

Deletions:

TRIM(IF a.billtofirstname > '' THEN a.billtofirstname ELSE a.firstname ENDIF) AS "First Name",
TRIM(IF a.billtolastname > '' THEN a.billtolastname ELSE a.lastname ENDIF) AS "Last Name",
TRIM("firstname" + ' ' + "lastname") AS "Full Name",
TRIM(IF a.billtoaddr1 > '' THEN a.billtoaddr1 ELSE b.Addr1 ENDIF) AS "Address Line1",
TRIM(IF a.billtoaddr2 > '' THEN a.billtoaddr2 ELSE b.Addr2 ENDIF) AS "Address Line2",

Revision [38]

Edited on 2008-10-24 11:40:05 by admin

Additions:

%%SELECT
OUTPUT TO c:\sos\billingaddresses.xls FORMAT EXCEL%%

Deletions:

##SELECT
OUTPUT TO c:\sos\billingaddresses.xls FORMAT EXCEL##

Revision [37]

The oldest known version of this page was created on 2008-10-24 11:33:25 by admin