WolfieeifloW WolfieeifloW - 1 month ago 7
SQL Question

Select Customers Who Purchased In X Month AND Y OR Z Month

I have a table that stores when customers have purchased something, and I am trying to find all customers who have purchased something in August 2016 (

X
) AND October 2016 (
Y
) OR November 2016 (
Z
) ---
X AND (Y OR Z)
.

I need the output to display the customers name, and the date of the purchase.

Customers are identified by
ALL_CUSTOMER_ID
and the date is stored in an
ARRIVAL_DATE
field (
Ex: 05-AUG-16
) in the table
PMS_FOLIO
.

Customers names are stored in the
NAME
field in the table
ALL_CUSTOMER
.

Answer

Use TO_CHAR() for the month, and the HAVING clause to filter :

SELECT t.cust_id
FROM purchases t
GROUP BY t.cust_id
HAVING COUNT(CASE WHEN TO_CHAR(DateField,'yyyymm') = '201608' THEN 1 END) > 0
   AND COUNT(CASE WHEN TO_CHAR(DateField,'yyyymm') IN ('201610','201611') THEN 1 END) > 0 

This will also return customers that had purchases both in October and November. If you want only purchases on one of them then :

HAVING COUNT(CASE WHEN TO_CHAR(DateField,'yyyymm') = '201608' THEN 1 END) > 0
   AND MAX(CASE WHEN TO_CHAR(DateField,'yyyymm') = '201610' THEN 1 ELSE 0 END) + 
       MAX(CASE WHEN TO_CHAR(DateField,'yyyymm') = '201611' THEN 1 ELSE 0 END) = 1