Re: Multiple WHERE conditions - Not sure about this post

On Mar 13, 11:06 am, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:
On Mar 13, 11:31 am, ame...@xxxxxxxxx wrote:



Hi,

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....

With a create table DDL with some sample data I am not going to spend
much time on this but how about:
Write a query to find the customers whose trail is complete
Write a query to find customers with active non-trail
Place each query into the from clause as an inline view
Join the two inline views in the query where clause on customer (and
maybe product based on your example)

HTH -- Mark D Powell --


Worth a try......


.

Relevant Pages

  • Re: Sum of numbers
    ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • Re: Sum of numbers
    ... Administrator to come in to insure we have this critical report by April. ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • Re: Sum of numbers
    ... Administrator to come in to insure we have this critical report by April. ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • Re: Make Relationship w/UNION-joined data?
    ... You can't create relationships between a query and a table. ... with different structures (although an SQL UNION ... >fo customers. ... Please respond in the newgroup and not by email. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Multiple WHERE conditions - Not sure about this post
    ... I killed my last post and am posting a new one with an updated query. ... So, customers who have a 'Complete' Trial ordered 60 days ago, and now ... Write a query to find customers with active non-trail ... Join the two inline views in the query where clause on customer (and ...
    (comp.databases.oracle.misc)
Loading