query

#1

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!

#2

On May 6, 4:54*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote:

Quote:

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 --

#3

On May 6, 4:54*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote:

Quote:

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 --

#4

On May 6, 4:54*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote:

Quote:

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 --

#5

On May 6, 4:54*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote:

Quote:

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 --

#6

On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:

Quote:

On May 6, 4:54 pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) 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

#7

On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:

Quote:

On May 6, 4:54 pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) 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

#8

On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:

Quote:

On May 6, 4:54 pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) 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

#9

On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:

Quote:

On May 6, 4:54 pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) 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

#10

On 7 May, 18:50, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) net> wrote:

Quote:

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

As Mark said, post the relevant CREATE TABLE script, together with an
INSERT script to populate the table and someone will be more inclined
to help with your query.

HTH

-g