On May 6, 4:54*pm, "ame...@iwc.net" <ame...@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 --