Hi AlanisN,
The ANY or ALL rule significantly changes the logic of the query generated. It is used when there is a one to many join between the main customer table and the table you are filtering (in your case the Contact History table). A customer might have numerous records in the contact history table, and you are looking for customers who have records with a contact date of last 3, 6 or 10 days. Some customers may have records that fall outside this range (for example 20 days, or 100 days). For example:
Customer 1: Contact Date 5 days ago, and 30 days ago
Customer 2: Contact Date 5 days ago
If your query is "in the last 6 days", and you use the ANY rule, the query will find customers with at least one record within the last 6 days, but may also have records outside of the last 6 days. In this example both Customer 1 and 2 will be selected.
If you query is "in the last 6 days", and you use the ALL rule, the query will find customers who only have records within the last 6 days (i.e. ALL of the contacts happened within the last 6 days). In this example only customer 2 will be selected.
The results of these two rules is very different.
The sql for these two rules is:
ANY:
PROC SQL;
Create table WORK.MA3 as
SELECT DISTINCT table0.CustomerID AS CustomerID LABEL='Customer ID'
FROM
CDM.CI_CONTACT_HISTORY table2 Inner join CDM.CI_CELL_PACKAGE table1
on table2.CELL_PACKAGE_SK = table1.CELL_PACKAGE_SK
Inner join DataMart.CUSTOMER_SUMMARY table3
on table3.CustomerID = table2.CustomerID
Inner join DataMart.CUSTOMER table0
on table0.CustomerID = table3.CustomerID
WHERE
table1.COMMUNICATION_CD = 'COMM265681' AND
(
(
(( table2.CONTACT_DTTM ) >= '29Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '30Jul2018:00:00:00'dt) /* 3 days ago */
OR (( table2.CONTACT_DTTM ) >= '26Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '27Jul2018:00:00:00'dt) /* 6 days ago */
)
OR (( table2.CONTACT_DTTM ) >= '22Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '23Jul2018:00:00:00'dt) /* 10 days ago */
)
;
quit;
ALL:
PROC SQL;
Create table WORK.MA4 as
SELECT
table4.CustomerID AS CustomerID LABEL='idFromSelection1__0 DIR'
FROM
(
SELECT DISTINCT
table0.CustomerID AS CustomerID LABEL='Customer ID',
( count(*) ) AS DII_16 LABEL=''
FROM
CDM.CI_CONTACT_HISTORY table2 Inner join CDM.CI_CELL_PACKAGE table1
on table2.CELL_PACKAGE_SK = table1.CELL_PACKAGE_SK
Inner join DataMart.CUSTOMER_SUMMARY table3
on table3.CustomerID = table2.CustomerID
Inner join DataMart.CUSTOMER table0
on table0.CustomerID = table3.CustomerID
WHERE
table1.COMMUNICATION_CD = 'COMM265681' AND
(
(
( ( table2.CONTACT_DTTM ) >= '29Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '30Jul2018:00:00:00'dt) /* 3 days ago */
OR ( ( table2.CONTACT_DTTM ) >= '26Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '27Jul2018:00:00:00'dt) /* 6 days ago */
)
OR ( ( table2.CONTACT_DTTM ) >= '22Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '23Jul2018:00:00:00'dt) /* 10 days ago */
)
GROUP BY
1
) table4 Inner join (
SELECT
table8.CustomerID AS DIR_1 LABEL='Customer ID',
( count(*) ) AS DII_2 LABEL=''
FROM
DataMart.CUSTOMER table8 Inner join DataMart.CUSTOMER_SUMMARY table9
on table8.CustomerID = table9.CustomerID
Inner join CDM.CI_CONTACT_HISTORY table7
on table9.CustomerID = table7.CustomerID
Inner join CDM.CI_CELL_PACKAGE table6
on table7.CELL_PACKAGE_SK = table6.CELL_PACKAGE_SK
GROUP BY
1
) table5 on (table4.CustomerID = table5.DIR_1 AND table4.DII_16 = table5.DII_2)
;
quit;
Cheers
James
