How to print mailing labels for customers needing turn-on service

Related Topics

 

 

The request

 

"Can you please make a command so that I can print out only customers who have used us within the past 2 years (or since 2012), deleting customers marked with no service.  I need their name and address."

 

Some clarifications

 

1. When she says "used us within the past 2 years", does she mean "used us for a turn-on", or for anything at all?

 

Default: any job at all

 

2. Should only DONE/CLOSED tickets be selected?

 

Default: all jobs selected

 

3. What about customers who are already on the schedule for a turn-on?

 

Default: skip these customers

 

4. Should duplicate customer addresses be skipped?

 

Default: skip duplicates

 

 

The SQL command:

 

select * from custlist where !noservice and exists(select * from tracklog where custlist.fileno = tracklog.fileno and  between(actiondate,ctod($$PROMPT_DATE$$),date())) and !exists (select * from tracklog where custlist.fileno = tracklog.fileno and component = 'TURN ON' and inlist(status,'INITIAL','OPEN') and between (actiondate,date(),date()+90) )

 

 

 is broken down as follows:

 

 

Command, broken down Means

select *

 

Select all fields

 

 from custlist

 

from customers table

 

where !noservice and

 

Select customers where the NOSERVICE flag is not checked

 

exists(select * from tracklog where custlist.fileno = tracklog.fileno and  between(actiondate,ctod("$$PROMPT_DATE$$"),date()))

 

and where the customer has ordered a job between a date in the past (operator will  enter 1/1/2012 when prompted) and "today"

 

and !exists (select * from tracklog where custlist.fileno = tracklog.fileno and component = 'TURN ON'  and inlist(status,'INITIAL','OPEN') and between (actiondate,date(),date()+90) )

 

and an OPEN job ticket for a TURN ON for this customer within the next 90 days does not exist

 

 

 

 

 

Screenshot sequence showing this SQL SELECT being used

 

 

Begin by selecting Print -> Mailing Labels -> Avery Labels

 

 

 

 

 

The Avery Address label printer screen will appear

 

 

 

 

Click SELECT RECORDS

 

 

 

Click SELECT USING SQL

 

The SQL SELECT COMMAND LIBRARY screen will appear. Check / select the SQL command created for this exercise

and click SELECT   (or click TEST to run the select and let you view the results)

 

 

 

 

 

A date prompt screen will appear.

 

Enter 1/1/2012

 

(click in month field, enter 1, click in day field, enter 1, click in year field enter 12, then click somewhere else on the form to see the date

get set to 1/1/2012)

 

Click OK

 

 

 

You will be returned to the Record Selection Manager screen with the record selection performed.

 

Note that the count in this case is 1,954 records selected of 4,818

 

 

 

Click SELECT

 

Since the "SKIP DUPLICATE ADDRESSES" option is checked, H2OS will now remove records with duplicate customer addresses

 

 

 

 

Click OK

 

 

Notice the PREVIEW checkbox is checked. This means the mailing labels

will appear on screen for examination before sending them to the actual

printer.

 

 

 

 

Click YES

 

Selected records will now appear as mailing labels.

 

Click the printer icon on the toolbar to send these labels to the printer

 

Click the CLOSE icon this toolbar to close this screen without printing.