Re: query
Date: Mon, 12 May 2008 06:30:01 -0700 (PDT)
Message-ID: <0cffad2e-ffe7-4cdc-8cbf-86777ed0c2a1@24g2000hsh.googlegroups.com>
On May 8, 2:40 pm, Urs Metzger <u..._at_ursmetzger.de> wrote:
> ame..._at_iwc.net schrieb:
>
> > On May 8, 1:45 pm, Urs Metzger <u..._at_ursmetzger.de> wrote:
> >> ame..._at_iwc.net schrieb:
>
> >>> On May 7, 11:44 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> >>>> On May 6, 4:54 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> >>>>> This simple query is driving me nuts.
> >>>>> I have a simple table:
> >>>>> customer_id
> >>>>> action_date
> >>>>> action
> >>>>> I want to get a distinct count of customer_id where the action = 'A'
> >>>>> and the MAX action date is between 01/01/2005 and 03/01/2005.
> >>>>> This seems simple, and here is my query:
> >>>>> SELECT MAX(action_date) action_date, count(distinct(customer_id))
> >>>>> FROM email_product_hist
> >>>>> WHERE action = 'A'
> >>>>> AND action_date BETWEEN TO_DATE('01012005','MMDDYYYY') AND
> >>>>> TO_DATE('03312005','MMDDYYYY')
> >>>>> AND customer_id NOT IN (SELECT customer_id FROM customer_account);
> >>>>> But people are telling me that this does not render the correct
> >>>>> results.......is there something I am missing??
> >>>>> Thanks!
> >>>> I suspect you need to group your data by trunc(action_date) and
> >>>> customer_id so you have a count of unique customers by date where the
> >>>> other conditions are also true.
> >>>> If you post a create table with a few rows of insert and show the
> >>>> desired results perhaps someone will take the time to correct your
> >>>> query.
> >>>> You should always specific the full Oracle version and edition as
> >>>> responses are often version specific.
> >>>> HTH -- Mark D Powell --
> >>> Thanks for the feedback Mark. Say I have this data:
> >>> Customer Id Action Date Status
> >>> ---------------------------------------
> >>> 12345678 12/01/2005 Active
> >>> 12345678 03/01/2005 Inactive
> >>> 12345678 01/01/2005 Active
> >>> 24568123 11/15/2005 Inactive
> >>> 33445566 03/01/2006 Active
> >>> 32548798 02/28/2005 Active
> >>> 77777733 02/15/2005 Inactive
> >>> 77777733 02/01/2005 Active
> >>> Now, basically I want to ignore row# 5 as it falls outside my range.
> >>> I also want to ignore row 4 as his status is inactive.
> >>> I want to include rows 6 as he is active, and row 1, as his MAX date
> >>> shows him active.
> >>> That is the key, that his MAX date still shows him active.
> >>> Row #7 will be ignored because his MAX date shows him as inactive....
> >>> Does that make more sense? And, we are on 10g R2....
> >>> Thanks again!
> >>> John
> >> As your setup script doesn't work, I've created on of my own:
>
> >> drop table email_product_hist;
> >> drop table customer_account;
> >> create table email_product_hist(Customer_Id number(10,0) not null,
> >> Action_Date date,
> >> Action varchar2(10),
> >> Status varchar2(10));
> >> insert into email_product_hist values(12345678, to_date('12/01/2005',
> >> 'MM/DD/YYYY'), 'A', 'Active');
> >> insert into email_product_hist values(12345678, to_date('03/01/2005',
> >> 'MM/DD/YYYY'), 'B', 'Inactive');
> >> insert into email_product_hist values(12345678, to_date('01/01/2005',
> >> 'MM/DD/YYYY'), 'A', 'Active');
> >> insert into email_product_hist values(24568123, to_date('11/15/2005',
> >> 'MM/DD/YYYY'), 'B', 'Inactive');
> >> insert into email_product_hist values(33445566, to_date('03/01/2006',
> >> 'MM/DD/YYYY'), 'A', 'Active');
> >> insert into email_product_hist values(32548798, to_date('02/28/2005',
> >> 'MM/DD/YYYY'), 'B', 'Active');
> >> insert into email_product_hist values(77777733, to_date('02/15/2005',
> >> 'MM/DD/YYYY'), 'A', 'Inactive');
> >> insert into email_product_hist values(77777733, to_date('02/01/2005',
> >> 'MM/DD/YYYY'), 'B', 'Active');
>
> >> create table customer_account(Customer_Id number(10,0) not null);
> >> commit;
>
> >> Now, although you don't say so, I guess you want to know for each
> >> customer_id the last status in a given date range with action
> >> code 'A' except for customers in the customer_account table:
>
> >> Here we go:
>
> >> SQL> select customer_id,
> >> 2 max(status) keep (dense_rank last order by action_date)
> >> 3 as status
> >> 4 from email_product_hist
> >> 5 where action = 'A'
> >> 6 and action_date between to_date('01012005','MMDDYYYY')
> >> 7 and to_date('03312005','MMDDYYYY')
> >> 8 and customer_id not in (select customer_id from customer_account)
> >> 9 group by customer_id;
>
> >> CUSTOMER_ID STATUS
> >> ----------- ----------
> >> 12345678 Active
> >> 77777733 Inactive
>
> >> As for the count distinct thing I'm completely lost. No idea what you
> >> mean.
>
> >> Its just a try...
>
> >> Urs Metzger
>
> > Close. They only want customers who's MAX action_date has a status of
> > A, and the date is within the date range....
>
> > I'm lost on how to incorporate the MAX thingy into it......
>
> OK, two more tries:
>
> #1:
> All customers except those from customer_account table
> whose last status was A
> and whose last action date is in a date range:
>
> SQL> select * from (
> 2 select customer_id,
> 3 max(status) keep (dense_rank last order by action_date)
> 4 as last_status,
> 5 max(action_date) as last_action_date
> 6 from email_product_hist
> 7 where customer_id not in (select customer_id
> 8 from customer_account)
> 9 group by customer_id)
> 10 where last_status = 'A'
> 11 and last_action_date between to_date('01012005','MMDDYYYY')
> 12 and to_date('03312005','MMDDYYYY');
>
> CUSTOMER_ID LA LAST_ACT
> ----------- -- --------
> 22222222 A 15.03.05
>
> #2:
> All customers except those from customer_account table
> whose last status within in a date range was A:
>
> SQL> select * from (
> 2 select customer_id,
> 3 max(status) keep (dense_rank last order by action_date)
> 4 as last_status,
> 5 max(action_date) as last_action_date
> 6 from email_product_hist
> 7 where customer_id not in (select customer_id
> 8 from customer_account)
> 9 and action_date between to_date('01012005','MMDDYYYY')
> 10 and to_date('03312005','MMDDYYYY')
> 11 group by customer_id)
> 12 where last_status = 'A';
>
> CUSTOMER_ID LA LAST_ACT
> ----------- -- --------
> 22222222 A 15.03.05
> 32547687 A 04.03.05
>
> You see, it's harder to define your requirements precisely then
> to code a piece of SQL ;-)
>
> hth,
> Urs Metzger
I have a question, look at your query vs. the other one:
select * from (
select customer_id,
max(action) keep (dense_rank last order by action_date desc)
as last_status,
max(action_date) as last_action_date
from email_product_hist
where customer_id not in (select customer_id
from customer_account)
group by customer_id)
where last_status = 'A'
and last_action_date between to_date('01012007','MMDDYYYY')
and to_date('03312007','MMDDYYYY');
---> 16,347
SELECT count(distinct(customer_id))
from email_product_hist e
WHERE action_date between to_date('01012007','MMDDYYYY') and
to_date('03312007','MMDDYYYY')
and e.action = 'A'
and e.customer_id NOT IN (
select customer_id from customer_account);
--> 11,204
Why the difference? I'm just doing a count of customer_id's where they are NOT in the customer_account table and have the date range and the status......supposedly the same as yours, just without any MAX date functions..... Received on Mon May 12 2008 - 08:30:01 CDT