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.

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

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.