Custom SQL Filters
From NewHaven Software Wiki
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):
Contents |
Mail List Filter requirements
SELECT DISTINCT C.* FROM CUST C WHERE ...
Invoice Filter requirements
SELECT DISTINCT I.* FROM INVOICE I, ORDERHDR O WHERE ... and I.order_id = O.order_id
Mail list filter examples
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 '2010-01-31' and '2010-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 > '2010-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 one 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 );
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' ))
Orders Placed from Selected Order Sources
SELECT DISTINCT c.* FROM CUST c, ORDERHDR o WHERE (o.ORDER_SRC in (SELECT ID FROM ORDERSRC WHERE name in ('Phoned In','Mailed In')) AND o.ORDER_DT BETWEEN '2011-01-31' AND '2011-10-31' AND o.CANCELLED = 0) AND (o.CUST_NUM = C.ID)
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)
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.
Invoice filter examples
- 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 a specific method (useful with Best Shipping since the order/invoice no longer has a shipping method)
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.ship_via in (SELECT ship_code FROM SHIPMETH WHERE SEL_LETTER = 'P')