Revision [455]
Last edited on 2011-05-13 14:56:51 by adminAdditions:
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.
Revision [454]
Edited on 2011-05-13 14:52:24 by adminAdditions:
======Mailing Labels For Payors In Collections======
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
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
Deletions:
comments here appear in italics
paste your tested query here
