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