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

On Mar 13, 11:31*am, ame... (AT) zacks (DOT) com wrote:
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 --

On Mar 13, 11:06*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:

Worth a try......

On Mar 13, 11:06*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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;

On Fri, 13 Mar 2009 08:31:13 -0700 (PDT), amerar (AT) zacks (DOT) com wrote:

Multiple posts with the same subject.
Getting real lazy lately, aren't we?

--
Sybrand Bakker
Senior Oracle DBA

ame... (AT) zacks (DOT) com wrote:
But do you want two rows per customer?

There are any number of ways to do this. A traditional
existance query would work...

select ...
from customer c
join customer_account ca on ...
where exists (select 1 from ... where ...)
and exists (select 1 from ... where ...)

Analytical functions aren't necessary AFAICS. Here's an
(obviously untested) group by with conditional count...

select c.customer_id,
first_name,
last_name,
email
from c.customer c
join customer_account ca on ca.customer_id = c.customer_id
join engine.customer_order co on co.customer_id = c.customer_id
join engine.order_line ol on ol.order_id = co.order_id
join engine.product p on p.product_id = ol.product_id
join product.newsletter_subscription
ns on ns.code = p.code
where p.subproduct_id = 197
group by
c.customer_id,
first_name,
last_name,
email
having sum(
case
when ns.subscr_type = 'Trial'
and ol.status = 'Complete'
and trunc(co.order_date) = trunc(sysdate) - 60
then 1
end ) > 0
and sum(
case
when ns.subscr_type <> 'Trial'
and ol.status <> 'Complete'
and trunc(co.order_date) = trunc(sysdate) - 30
then 1
end ) > 0

--
Peter

amerar (AT) zacks (DOT) com schreef:

Yeah sure, and you'll post the next task under a different name......


Shakespeare

On Fri, 13 Mar 2009 10:09:24 -0700 (PDT), amerar (AT) zacks (DOT) com wrote:

And you are getting paid for it, so do what you are paid for.

--
Sybrand Bakker
Senior Oracle DBA