Re: Multiple WHERE conditions - Not sure about this post

From: <amerar_at_zacks.com>
Date: Fri, 13 Mar 2009 10:09:24 -0700 (PDT)
Message-ID: <20df45bc-7747-46c4-8029-960271012639_at_a39g2000yqc.googlegroups.com>

On Mar 13, 11:06 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Mar 13, 11:31 am, ame..._at_zacks.com 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 --

Mark,

Looks like your suggestion does 99% of what I need. The query is below. Since I want to filter it to only groups with 2 records, I need to find a way to do that. It is the last task.

Thanks for your idea.

SELECT customer_id, first_name, last_name, email, order_date, order_id, product_id, code, status, subscr_type, cnt FROM (SELECT c.customer_id, first_name, last_name, email, order_date, ol.order_id, p.product_id, p.code, ol.status, subscr_type, COUNT(*)

      OVER (PARTITION BY co.customer_id) cnt
      FROM customer c, customer_account ca, engine.customer_order co,

engine.order_line ol, engine.product p,
product.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.subproduct_id = 197
        AND p.code = ns.code
        AND ns.subscr_type =  'Trial'
        AND TRUNC(co.order_date) = TRUNC(SYSDATE - 60) AND ol.status
=  'Complete')
      UNION
     (SELECT c.customer_id, first_name, last_name, email, order_date,
ol.order_id, p.product_id, p.code, ol.status, subscr_type, COUNT(*)
      OVER (PARTITION BY co.customer_id) cnt
      FROM customer c, customer_account ca, engine.customer_order co,

engine.order_line ol, engine.product p,
product.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.subproduct_id = 197
        AND p.code = ns.code
        AND ns.subscr_type <>  'Trial'
        AND TRUNC(co.order_date) = TRUNC(SYSDATE - 29) AND ol.status

<> 'Complete')
ORDER BY customer_id, order_id; Received on Fri Mar 13 2009 - 12:09:24 CDT