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.
The COALESCE function in the collection date condition is there to assign a value to payors with NULL collection dates. We use an arbitrary date that should always be outside of the desired selection range to prevent those rows from being included in the results or preventing any results from being delivered because of the ambiguous nature of NULL values.
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
sos.payors a
JOIN sos.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
