CODEWITHSUNDEEP
sqldatabaseoracle-databaseselect
WolfieeifloW
WolfieeifloW

Reputation: 619

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.

Upvotes: 1

Views: 398

Answers (2)

sagi
sagi

Reputation: 40481

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269613

Use group by and having. Your question provides neither sample data nor a database tag, but here is the idea;:

select customerid
from t
group by customerid
having sum(case when month(date) = 8 then 1 else 0 end) > 0 and
       sum(case when month(date) in (10, 11) then 1 else 0 end) > 0;

You probably want to include the year as well in the comparison.

Upvotes: 0

Related Questions