Re: query

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 -- Received on Wed May 07 2008 - 11:44:54 CDT