Help with Query
Date: Tue, 22 Apr 2008 13:52:43 -0700 (PDT)
Please take a look at this query:
SELECT max(action_date), customer_id
WHERE action = 'A'
AND action_date BETWEEN TO_DATE('01012007','MMDDYYYY') AND TO_DATE('03312007','MMDDYYYY')
AND email_product_id = 'PPM'
AND customer_id NOT IN (SELECT customer_id FROM new_customer.customer_account)
GROUP BY customer_id;
It works. However, we have a table, CUSTOMER, which contains the customers email address. It is joined to the CUSTOMER_ACCOUNT table by the customer ID. It is also joined to the EMAIL_PRODUCT_HIST by customer_id.
What I am looking to do is get the email address from the CUSTOMER table for customers who are NOT in the CUSTOMER_ACCOUNT table, and, are IN the EMAIL_PRODUCT_HIST table that have the above criteria for the MAX date.
CUSTOMER_ID is unique in the CUSTOMER & CUSTOMER_ACCOUNT tables. It is NOT unique in the EMAIL_PRODUCT_HIST table. So, I'm looking to get the email, for the customer who has an action of 'A' and a email_product_id of 'PPM' for his max(action_date), and does not exist in the CUSTOMER_ACCOUNT table.
Hope that all makes sense. If this cannot be done, I can just write a block of code to do it.
John Received on Tue Apr 22 2008 - 15:52:43 CDT