On May 7, 11:44 am, Mark D Powell <Mark.Pow...@[EMAIL PROTECTED]
> wrote:
> On May 6, 4:54 pm, "ame...@[EMAIL PROTECTED]
" <ame...@[EMAIL PROTECTED]
> 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
|
