Date: Wed, 7 May 2008 10:50:02 -0700 (PDT)
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
> 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....
John Received on Wed May 07 2008 - 12:50:02 CDT