From NewHaven Software Wiki

Jump to: navigation, search

In addition to the standard invoice and mail list filter criteria provided in CMS, you also have the option to write your own SQL. This is useful for when you'd want to create a filter that is beyond the standard options available through the CMS interface.

It is important to note that when you select the 'Write Own SQL' checkbox, it supersedes all other criteria and becomes the only enabled option on the filter screen. You cannot combine it with other filter criteria. You can, however, customize a mail filter created in CMS.

In the Mail List Filter section you have the option of creating a filter using the checkboxes to get as close as you can to your desired filter. Once saved you would then click on View SQL to see the resulting SQL CMS has created based on the options selected. You cannot edit that SQL there but you can copy it and create a new filter with 'Write Own SQL' and paste/edit it there.

This can be very handy and is rather easy to do as, often times, the only modification desired is a change in Boolean values. For example, if you create a filter based on two customer description codes (CDC's) the default Boolean is 'OR'. In other words, customers will qualify for this filter if they have one code OR the other. If you only wanted customers who had both codes, you would create a custom filter, paste in the SQL from the original filter and change the OR to an AND.

Syntax is very important in these filters so if you are writing one from scratch, please pay attention to these examples for guidance on what your Selects should look like (namely the use of DISTINCT and which tables are being referenced):

Mail List Filters

Requirements

Every mail list filter must include (start with) at least the following:

SELECT DISTINCT C.*
FROM CUST C
WHERE ...

Mail list filter examples

NOTE: When writing your own SQL keep in mind if you are going to be using these statements in Compound Mail List Filters, you will need to return C.ID, not C.* This makes these individual filters suitable for use only as components for Compound filters. They can no longer be used on their own. Our recommendation is to label them as compound filter components. Like COMP_Products_By_Date and COMP_Not_Select_Products. Doing this you will know not to use them by themselves. To use the filters by themselves and to combine different filters together, you have to use joins and not compound filters.

Products Purchased

Target customers based on products fulfilled within a date range (3 examples) and thus excludes unfulfilled back orders and future ships. Date range is based off of the fulfill date. These filters also exclude fully returned/canceled items.

In a Date Range

SELECT DISTINCT C.*
FROM CUST C, ITEMSORD I
WHERE (I.PROD_CODE = '1'
AND I.FULFILL_DT between '2017-01-31' and '2017-03-31'
AND ((I.CUR_RETURN + I.CUR_CANCEL) < I.CUR_SENT))
 AND (I.CUST_NUM = C.ID)

Greater than a specified date

SELECT DISTINCT C.*
FROM CUST C, ITEMSORD I
WHERE (I.PROD_CODE = '1'
AND I.FULFILL_DT > '2017-01-31'
AND ((I.CUR_RETURN + I.CUR_CANCEL) < I.CUR_SENT))
 AND (I.CUST_NUM = C.ID)

Ordered in the last 30 days

SELECT DISTINCT C.*
FROM CUST C, ITEMSORD I
WHERE (I.PROD_CODE = '1'
AND I.FULFILL_DT > (TODAY(*) - 30)
AND ((I.CUR_RETURN + I.CUR_CANCEL) < I.CUR_SENT))
 AND (I.CUST_NUM = C.ID)

Sold by a specific salesperson, only shipped and not returned (no date range)

SELECT DISTINCT C.*
FROM CUST C INNER JOIN orderhdr o ON c.id = o.cust_num
INNER JOIN itemsord i ON o.order_id = i.order_id
WHERE o.proforma <> 1
AND o.tempsave <> 1
AND i.status > 5
AND i.order_qty > (i.cur_return + cur_cancel)
AND (i.prod_code = 'PRODUCT CODE'
AND o.SALES_ID = 'Salesperson Username')

Customers that bought from a specified list of products in a selected date range. Exclude if they bought any from another list of products or if the customer is set to Do Not Mail Catalog.

(this query has to look at your entire order history and will take a long time to run)

SELECT DISTINCT C.* FROM CUST C
LEFT JOIN CUSTFLAGS CF ON CF.ID = C.ID AND CF.FLAG_ID = 1
JOIN ITEMSORD I ON I.CUST_NUM = C.ID
WHERE I.FULFILL_DT BETWEEN '2012-02-01' AND '2013-03-07'
AND (I.CUR_RETURN + I.CUR_CANCEL) < I.CUR_SENT
AND I.PROD_CODE IN ('PRODCODE-A','PRODCODE-B')
-- FLAG_ID <> 1 by matching on null with the left join
AND CF.FLAG_ID IS NULL 
AND C.ID NOT IN 
(SELECT IO.CUST_NUM FROM ITEMSORD IO WHERE IO.PROD_CODE IN (
-- Comma delimited list of product codes to match for exclusion from the customer list
'PRODCODE-X','PRODCODE-Y','PRODCODE-Z')
GROUP BY IO.CUST_NUM 
-- End customer exclusion list
);

Customers that bought a specific product that was shipped since a specific date that hasn't been returned and that customer has a viable email address.

SELECT DISTINCT C.* 
FROM CUST C
Join Itemsord t ON t.cust_num = c.ID
Join Invoice i ON i.order_id = t.order_id and i.invoice_id = t.invoice_id
WHERE (t.PROD_CODE = 'XXXXXX'
and i.SHIPPED_DT between '2017-07-31' and '2017-08-31'
AND ((t.SENT_QTY > t.RET_QTY - t.CANCEL_QTY)))
AND datalength(LTRIM(RTRIM([EMAIL]))) - datalength(REPLACE(LTRIM(RTRIM([EMAIL])),'@','')) = 1 AND (CHARINDEX('.@',[EMAIL]) = 0 AND CHARINDEX('..',[EMAIL]) = 0)

CMS Customer Filter for customers with a specific item on Back Order

Click the title link to redirect to an article specifically on this topic.

Targeting Catalog Requestors

Click the title link to redirect to an article specifically on this topic.

Customers with Unshipped Orders in a Date Range

SELECT DISTINCT C.*
FROM CUST C 
LEFT OUTER JOIN ORDERHDR O ON (C.ID = O.CUST_NUM)
WHERE
((O.order_dt BETWEEN '2012-05-18' AND '2013-09-18')
AND (isnull(o.shipped_dt,'1899-12-30') = '1899-12-30'))
AND (C.EMAIL <> '')

By First Order Date

SELECT DISTINCT C.*
FROM CUST C
WHERE
(C.FIRST_DATE BETWEEN '2013/08/15' AND '2013/08/15')

Phone number Area code

SELECT DISTINCT C.*
FROM CUST C 
WHERE
(C.ID IN (SELECT CUST_NUM FROM PHONES
WHERE phone_num like 
'(297%'
or phone_num like 
'297'
or phone_num like 
'1297'
or phone_num like 
'1-297'
))

Customers having more than one Customer Description Code

In CMS it is easy to find customers who have any one code, or one of several, but you'll need custom SQL like the following to find customers who must have more than one of a list of codes to qualify.

SELECT DISTINCT C.*
FROM CUST C  
WHERE C.ID IN (
  SELECT CUST_NUM FROM CUSTDESC
  WHERE CUSDESKE in ('0525','0600')
  GROUP BY CUST_NUM
  HAVING COUNT(CUST_NUM) >= 2 -- 2 = the number of values to find
)

Change the cusdeske values to correspond to the desired customer description codes (CDC) per the CUSDESCO table
Change the integer in the last line to indicate the number of CDC matches required

Customers with a Company Name

SELECT DISTINCT C.*
FROM CUST C
WHERE C.COMPANY <> ''

Last Modified

The Last Modified field was added in CMS 8.0 but does not yet have a corresponding mail filter option. Here are a couple of examples of how you can build this into your filters:

Last Modified in a range of dates:

SELECT DISTINCT C.*
FROM CUST C 
WHERE (C.LAST_UPDAT BETWEEN '2011/05/02' AND '2012/05/02')

Change the dates as needed.

Last Modified in the last 180 days:

SELECT DISTINCT C.*
FROM CUST C 
WHERE (C.LAST_UPDAT > (TODAY(*) - 180))

Change 180 to whatever number of days back is desired.

All Recipients for a Customer (e.g. Amazon)

If you are processing orders with Amazon or similar partner, you'll only get shipping addresses and not billing information from them since Amazon handles all of the payments. As such these orders will import into CMS with Amazon as the customer and always attach themselves to your Amazon customer record. If you have the option enabled in CMS Order Entry Options, new customer records will also be created for each ship-to. It may be helpful to be able to identify those ship-to customer records. This might be to tag them with a customer description code, exclude from an upcoming mailing, etc. This SQL can be used as a mail list filter to find those recipient customer records that have ordered through Amazon (or whoever your orders are coming from.)

SELECT DISTINCT C.*
FROM CUST c
WHERE C.ID in
(SELECT a.cust_num
FROM Address a
Join recipient r ON r.address_id = a.address_id
Join recipient_order ro ON ro.recipient_id = r.recipient_id
Join orderhdr o ON o.order_id = ro.order_idWhere o.cust_num = '0000518128')

Change the customer number in the last line to match your billing account for Amazon. Zero pad to ten total characters as shown in the above example.

Orders Placed from Selected Order Sources

SELECT DISTINCT c.* 
FROM CUST c 
Join ORDERHDR o ON o.cust_num = c.ID
Join ORDERSRC s ON s.ordersrc_id = o.order_src
WHERE (o.ORDER_DT BETWEEN '2016-01-31' AND '2017-10-31' 
AND o.CANCELLED = 0
and (s.name = 'Phoned In'
or s.name = 'Mailed In'))

Amazon Recipients

Similar to the above, this is targetting a specific order source but, since you are really drop-shipping for Amazon, Amazon is your customer...so all of the orders fall under the Amazon customer record in your mail list. If you have CMS configured to save recipients as new customers though, CMS will have customer records for each customer that ordered via Amazon. The following query will target those recipients:

SELECT DISTINCT C.*
FROM CUST C 
WHERE c.ship_id > 0
and c.ship_id in
(SELECT address_id FROM Recipient r
JOIN Recipient_order ro ON r.recipient_id = ro.recipient_id
JOIN ORDERHDR o ON o.order_id = ro.order_id
WHERE o.order_src = 15)

Noting that you'll need to change the last line (15) to match whatever the order source ID is for the order source you're targeting (see the ORDERSRC table for this.)

Customers with orders having a specific Referral Code

SELECT DISTINCT c.* 
FROM CUST c 
Join ORDERHDR o ON o.cust_num = c.ID
WHERE (o.ORDER_DT BETWEEN '2016-01-31' AND '2017-06-30') 
AND o.referral_code = 'XXXX'

Invoice created in a date range with a captured credit card but invoice is unshipped

SELECT DISTINCT C.*
FROM CUST C, INVOICE I
JOIN Payledger p ON p.order_id = i.order_id
and p.invoice_id = i.invoice_id
JOIN chargeinfo r ON p.payid = r.payid
WHERE I.ORDER_DT between '2012-08-31' and '2013-02-28'
AND I.SHIPPED_FL = '0'
AND r.edc = 1
AND (I.CUST_NUM = C.ID)

Customers with a declined credit card in last 30 days

SELECT DISTINCT C.*
FROM CUST C
JOIN paylist l ON l.ID = C.ID
JOIN paytypes t ON t.pay_type = l.pay_type
WHERE l.badpayment = '1'
AND l.DELETED = '0'
AND t.credit_car = '1'
AND l.createdate > (TODAY(*)-30)

Filter based on one of the customer user defined fields where the value is greater than X

SELECT DISTINCT C.*
FROM CUST C
WHERE (REGEXP_SUBSTR(C.USERCODE1,'\d+\.*\d*') > 12.4)

This method allows you to perform a greater or less than query on a user field which is not explicitly defined as numeric. That said, this filter will fail if you have any non-numeric values in the corresponding user-defined field in any of the customer records.
It is important to understand that any time you are running a "greater than" or "less than" operator against a numeric value you must not use quotes. Otherwise SQL will interpret that as a string comparison. And string comparing numeric values will not be accurate.

U.S. and Canadian customers with first contact date, catalog request, or order in date range

The first half of the SQL is to include based on any of the contact dates AND that they are in U.S. or Canada. The second half (starting with AND NOT) is the exclude portion of the statement, in this case based on specific customer description code, customer flag, or or order's adcodes.

SELECT DISTINCT C.* 
FROM CUST C 
WHERE C.COUNTRY < 2
AND (
  (C.FIRST_CONTACT BETWEEN '2012-01-01' AND '2015-09-30')
  OR EXISTS (SELECT 1 FROM CUSTHIST WHERE CUSTHIST.CUST_NUM = C.ID AND ENTRYDATE > '2012-01-01') 
  OR EXISTS (SELECT 1 FROM TODO WHERE TODO.TYPE = '05' AND TODO.VOID = 0 AND TODO.DATE_CREAT > '2012-01-01' AND TODO.RELATES_TO = C.ID)
  OR (C.LAST_INVOI BETWEEN '2008/01/01' AND '2015/10/12')
) AND NOT EXISTS ( SELECT 1 FROM CUST C2 
  LEFT OUTER JOIN CUSTDESC ON C2.ID = CUSTDESC.CUST_NUM 
  LEFT OUTER JOIN ORDERHDR ON C2.ID = ORDERHDR.CUST_NUM
  LEFT OUTER JOIN CUSTFLAGS CF ON CF.ID = C2.ID
  WHERE (
    CUSTDESC.CUSDESKE = '0004'
    OR ORDERHDR.ADCODE = 'p-etch' 
    OR ORDERHDR.ADCODE = 'wwwg'
    OR CF.FLAG_ID = 1
  ) AND C2.ID = C.ID
)

Customers with a specific term that have no spending in period

Here we're looking for customers that you have on terms but have not spent anything in a given time period. Adjust date ranges, spending amounts, and the term_id in the SQL to suit.

SELECT DISTINCT C.*
FROM CUST C  
LEFT OUTER JOIN ORDERHDR ON (C.ID = ORDERHDR.CUST_NUM)
WHERE c.term_id  = '002'
and not fbCustSpendingInPeriod(c.id,'2018/09/08','2020/09/08',1,99999999) =1

Invoice filters

Invoice Filter requirements

Every invoice filter must include (start with) at least the following:

SELECT DISTINCT I.* FROM INVOICE I 
JOIN ORDERHDR O ON (I.ORDER_ID=O.ORDER_ID) 
LEFT OUTER JOIN PACKAGE P ON (I.ORDER_ID=P.ORDER_ID) AND (I.INVOICE_ID=P.INVOICE_ID) 
LEFT OUTER JOIN ADDRESS ON (I.SHIP_ID = ADDRESS.ADDRESS_ID)
WHERE...

Examples

Target invoices whose item total is greater than $X

SELECT DISTINCT I.* FROM Invoice I, Orderhdr O
WHERE I.ITEM_TOT > 300
and I.order_id = O.order_id

Modify the 300 in this example to represent the desired item total threshold. This item total take into account line and order level discounts but does not include S&H, taxes, or other order level charges.

Target a single customer and any of their invoices with a balance

SELECT DISTINCT I.* FROM INVOICE I 
JOIN ORDERHDR O ON (I.ORDER_ID=O.ORDER_ID) 
LEFT OUTER JOIN PACKAGE P ON (I.ORDER_ID=P.ORDER_ID) AND (I.INVOICE_ID=P.INVOICE_ID) 
LEFT OUTER JOIN ADDRESS ON (I.SHIP_ID = ADDRESS.ADDRESS_ID)
WHERE O.CUST_NUM = '0000075194'
AND I.BALANCE > 0

Target invoices shipping to Canada

SELECT DISTINCT I.* FROM Invoice I, Address A, Orderhdr O
WHERE I.SHIP_ID = A.ADDRESS_ID
and A.COUNTRY = '001'
and I.order_id = O.order_id

Target invoices with packages shipping via specific methods

This is useful with Best Shipping since the order/invoice no longer has a shipping method or if you're trying to filter on the actual shipping choice used instead of the underlying service level (which is how the shipping method filters work normally)

SELECT DISTINCT I.* FROM Invoice I, Package P, Orderhdr O
WHERE I.order_id = P.order_id and I.invoice_id = P.invoice_id
and I.order_id = O.order_id
and P.shipmethid in
(SELECT shipmethid FROM SHIPMETH
WHERE SEL_LETTER in ('C','UC'))

Targets fulfilled invoices with a specific adcode

Specifically that does not use a selected term and the order does not contain an item ending with M:

SELECT DISTINCT I.* FROM INVOICE I, ORDERHDR O
WHERE I.order_id = O.order_id
AND O.adcode = ' HOMESHOW'
AND O.term_id <> '002'
AND I.fulfill_dt > '2010-01-01'
AND I.order_id|I.invoice_id NOT IN
(SELECT order_id|invoice_id 
FROM itemsord
WHERE fulfill_dt > '2010-01-01'
AND prod_code LIKE '%M')

Single Line Orders

The technique used here to identify which invoice are single product is unusual and noteworthy. There is a join against a sub-select because a sub-select on its own is only useful when looking for a single field and in this case we need both order_id and invoice_id. The last line is optional and should only be used when targeting specific products.

SELECT DISTINCT I.* FROM Invoice I
JOIN ORDERHDR O ON O.order_id = I.order_id
JOIN ITEMSORD T ON T.order_id = I.order_id AND T.invoice_id = I.invoice_id
JOIN (SELECT order_id, invoice_id FROM itemsord
GROUP BY ORDER_ID, Invoice_id
HAVING COUNT(line_id) = 1) j ON j.order_id = I.order_id AND j.invoice_id = I.invoice_id
WHERE I.TRANS_TYPE <> 'R' --Excluding returns
AND T.PROD_CODE IN ('SUB1','SUB2','SUB3')

Single Qty Item

Similar to above, which is for single line orders but for any quantity ordered, the following looks for single quantity ordered as well. As above, the last line is optional and should only be used when targeting specific products.

SELECT DISTINCT I.* FROM Invoice I
JOIN ORDERHDR O ON O.order_id = I.order_id
JOIN ITEMSORD T ON T.order_id = I.order_id 
AND T.invoice_id = I.invoice_id 
AND t.sent_qty =1 
AND (t.ret_qty + t.cancel_qty) = 0
JOIN (SELECT order_id, invoice_id FROM itemsord
GROUP BY ORDER_ID, Invoice_id
HAVING COUNT(line_id) = 1) j ON j.order_id = I.order_id AND j.invoice_id = I.invoice_id
WHERE T.PROD_CODE IN ('SUB1','SUB2','SUB3')

Invoices by State and Carrier

We wrote this for invoice filtering by state. This shows the use of a sub-select that is not part of a join:

SELECT DISTINCT I.* FROM Invoice I
JOIN Orderhdr O ON O.order_id = I.order_id
JOIN Address A ON A.ADDRESS_ID = I.SHIP_ID
WHERE A.STATE IN
('ME','NH','VT','MA','CT','RI','NJ','DE','MD','DC','NY','PA','VA','NC','SC','GA','TN','KY','WV','OH','AL')
AND O.SHIP_VIA IN (SELECT ship_code FROM SHIPPERS WHERE SHIPPER = 'UPS')

Invoices using a specific shipping method AND have at least one item in a bin location range

SELECT DISTINCT I.* FROM INVOICE I
JOIN ORDERHDR O ON O.ORDER_ID = I.ORDER_ID
JOIN (
SELECT IO.INVOICE_ID, IO.ORDER_ID FROM SKUWARE S 
JOIN ITEMSORD IO ON IO.SKU_ID = S.SKU_ID
WHERE REGEXP_SUBSTR(S.BINLOC,'\d+\.\d') BETWEEN 3.0 AND 3.9
GROUP BY IO.ORDER_ID, IO.INVOICE_ID, IO.WAREHOUSE
) BIN ON BIN.INVOICE_ID = I.INVOICE_ID AND BIN.ORDER_ID = I.ORDER_ID
WHERE I.SHIPMETH_USED = 'First Class Mail'

Invoices using a specific shipping method AND have only items in a bin location range

This assumes bin locations are numeric:

SELECT DISTINCT I.* FROM INVOICE I 
JOIN ORDERHDR O ON O.ORDER_ID = I.ORDER_ID
JOIN (SELECT IO.INVOICE_ID, 
  IO.ORDER_ID, IO.WAREHOUSE, 
  REGEXP_SUBSTR(S.BINLOC,'\d+\.*\d*') AS BINLOC_NUM, 
  COUNT(IO.ITEMID) AS ITEMS
  FROM SKUWARE S JOIN ITEMSORD IO ON IO.SKU_ID = S.SKU_ID
  GROUP BY IO.ORDER_ID, IO.INVOICE_ID, IO.WAREHOUSE, S.BINLOC
  HAVING BINLOC_NUM BETWEEN 3.0 AND 3.9
) IC ON IC.ORDER_ID = I.ORDER_ID AND IC.INVOICE_ID = I.INVOICE_ID
WHERE IC.ITEMS = (    
  SELECT COUNT(*) FROM ITEMSORD IO 
  WHERE IO.ORDER_ID = I.ORDER_ID 
  AND IO.INVOICE_ID = I.INVOICE_ID
) 
AND I.SHIPMETH_USED = 'First Class Mail'

Custom SQL Filters