Re: query

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 Received on Wed May 07 2008 - 12:50:02 CDT