Multiple WHERE conditions - Not sure about this post
Date: Fri, 13 Mar 2009 08:31:13 -0700 (PDT)
I killed my last post and am posting a new one with an updated query. Hopefully the other one was deleted.
Anyhow, I want to retrieve 2 records based on different criteria and am hoping not to do a union or anything since 95% of the criteria is the same.
SELECT customer_id, first_name, last_name, email, order_date,
order_id, status, subscr_type
FROM (SELECT c.customer_id, first_name, last_name, email, order_date, ol.order_id, p.product_id, p.code, ol.status, subscr_type, ROW_NUMBER
OVER (PARTITION BY c.customer_id ORDER BY c.customer_id) cnt FROM customer c, customer_account ca, customer_order co, order_line ol, product p, newsletter_subscription ns WHERE c.customer_id = co.customer_id AND c.customer_id = ca.customer_id AND co.order_id = ol.order_id AND ol.product_id = p.product_id AND p.code = ns.code AND p.subproduct_id = 197 AND (ns.subscr_type = 'Trial' AND TRUNC(co.order_date) = TRUNC
(SYSDATE - 60) AND ol.status = 'Complete')
AND (ns.subscr_type <> 'Trial' AND TRUNC(co.order_date) = TRUNC
(SYSDATE - 29) AND ol.status <> 'Complete'))
ORDER BY customer_id, order_id;
But, what I am really looking for is that I want customers who have purchased a 'Trial' 60 days ago with a status of 'Complete', and ALSO have on their account a 'Non-Trial' order with a status of 'Active' or Cancelled' purchased 30 days ago.
So, customers who have a 'Complete' Trial ordered 60 days ago, and now are 'Active' or 'Cancelled' with a Non-Trial ordered 30 days ago.
I'm hoping to do this in one query using analytical functions of something, rather than some union or PL/SQL code.... Received on Fri Mar 13 2009 - 10:31:13 CDT