Re: query

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

Well, time to turn this over to the customer.....I'll return today and let you know the results.

Thanks for everyones help!

Also, what is 'KEEP', I've never seen that before..... Received on Mon May 12 2008 - 07:59:18 CDT