Re: query

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...... Received on Thu May 08 2008 - 13:55:55 CDT