Revision [454]
This is an old revision of LabelsForPayorsInCollections made by admin on 2011-05-13 14:52:24.Mailing Labels For Payors In Collections
Back to Query Directory
I need mailing labels for Payors with a collection date of 1/1/11 and greater, excluding accounts that are paid in full.
The date that a payor is sent to collections is a field in the payors table. The balance owed by the payor is a sum of the field "postedbal" in a related table, ptpayors. The primary key that uniquely identifies a payor is the payornum value. JOIN the two tables together using this shared value, payornum, which is found in both tables.
There may be more than one ptpayors row for each payor, so to eliminate duplicate rows in the output (which will subsequently be loaded into Excel and used as data for a MailMerge label in Word), we add the DISTINCT modifier after SELECT. That will have the effect of removing any duplicate rows.
SELECT DISTINCT
TRIM(a.firstname) AS "First Name",
TRIM(a.payorname) AS "Last Name",
a.addr1 AS "Address 1",
a.addr2 AS "Address 2",
TRIM(city) AS "City",
TRIM(state) AS "State",
zip
FROM
payors a
JOIN ptpayors b ON a.payornum = b.payornum
WHERE
COALESCE(CollectDate,'1990-01-01') >= '2011-01-01'
AND b.postedbal > 0
ORDER BY
"Last Name", "First Name"
;
OUTPUT TO c:\sos\labels.html FORMAT HTML
TRIM(a.firstname) AS "First Name",
TRIM(a.payorname) AS "Last Name",
a.addr1 AS "Address 1",
a.addr2 AS "Address 2",
TRIM(city) AS "City",
TRIM(state) AS "State",
zip
FROM
payors a
JOIN ptpayors b ON a.payornum = b.payornum
WHERE
COALESCE(CollectDate,'1990-01-01') >= '2011-01-01'
AND b.postedbal > 0
ORDER BY
"Last Name", "First Name"
;
OUTPUT TO c:\sos\labels.html FORMAT HTML
CategoryQueries
