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!

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

On May 7, 11:44 am, Mark D Powell <Mark.Pow...@eds.com> wrote:
> 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 --

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

On 7 May, 18:50, "ame...@iwc.net" <ame...@iwc.net> wrote:
> 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

On May 8, 6:32 am, gazzag <gar...@jamms.org> wrote:
> On 7 May, 18:50, "ame...@iwc.net" <ame...@iwc.net> wrote:
>
>
>
> > 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

I do not understand why the CREATE table will help here. The three
columns of interest are above. If you see the CREATE statement, how
does that help with the query? Those are the only three columns which
are related here......

Any why the INSERT also?

John

On May 8, 7:33 am, "ame...@iwc.net" <ame...@iwc.net> wrote:
> On May 8, 6:32 am, gazzag <gar...@jamms.org> wrote:
>
>
>
> > On 7 May, 18:50, "ame...@iwc.net" <ame...@iwc.net> wrote:
>
> > > 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
>
> I do not understand why the CREATE table will help here. The three
> columns of interest are above. If you see the CREATE statement, how
> does that help with the query? Those are the only three columns which
> are related here......
>
> Any why the INSERT also?
>
> John

It would help us help you if we could reproduce your problem without
having to write everything including the test data ourselves.

I tried yesterday but TOAD crashed on me. The solution I was checking
was essentially becoming a series of nested queries.
First is one to find the max date for each customer (simple group by).
Using that as a view, find the Active customers (join view and base
table on customer id and where base table status=Active)

Get that written and I think you will be just about there. In the
final query, I'll bet you do not need the DISTINCT.

Ed
(that is a clue I often see when people show me queries that do not
work. When I see DISTINCT, Most often it means that not all the
conditions were included.)

On 8 May, 13:33, "ame...@iwc.net" <ame...@iwc.net> wrote:
> I do not understand why the CREATE table will help here. *The three
> columns of interest are above. *If you see the CREATE statement, how
> does that help with the query? *Those are the only three columns which
> are related here......
>
> Any why the INSERT also?
>
> John- Hide quoted text -
>
> - Show quoted text -

The scripts would help me to help recreate your problem and allow me
to test my query. That way, we can be sure that we're singing from
the same hymn sheet, as it were.

However, if you can't be bothered to do that, I can't be bothered to
spend any time on it myself, either.

I'm out.

-g

On May 8, 8:21 am, gazzag <gar...@jamms.org> wrote:
> On 8 May, 13:33, "ame...@iwc.net" <ame...@iwc.net> wrote:
>
> > I do not understand why the CREATE table will help here. The three
> > columns of interest are above. If you see the CREATE statement, how
> > does that help with the query? Those are the only three columns which
> > are related here......
>
> > Any why the INSERT also?
>
> > John- Hide quoted text -
>
> > - Show quoted text -
>
> The scripts would help me to help recreate your problem and allow me
> to test my query. That way, we can be sure that we're singing from
> the same hymn sheet, as it were.
>
> However, if you can't be bothered to do that, I can't be bothered to
> spend any time on it myself, either.
>
> I'm out.
>
> -g

Understood. In the example below, I would want to ignore customer
35666699 because he falls out of the date range, 01/01/2005 -
12/31/2005. I'd ignore customer 21435557 because his status is D. I
would include customer 32547687 because his MAX date has a status of
A. Likewise, I'd want customer 35666687 since his MAX date has a
status of A. I would not want customer 22222222 because his MAX date
has a status o D.

Hopefully I got it right.......


CREATE TABLE EMAIL_PRODUCT_HIST (
ACTION_TYPE VARCHAR2(2),
STATUS VARCHAR2(2),
ACTION_DATE DATE,
EMAIL_HIST_ID NUMBER CONSTRAINT EMAIL_PRODUCT_HIST_HIST_ID_NN
NOT NULL,
CUSTOMER_ID NUMBER CONSTRAINT EMAIL_PRODUCT_HIST_CUST_ID_NN
NOT NULL,
EMAIL_PRODUCT_ID VARCHAR2(5));

INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('09032005','MMDDYYYY'),32547687,' PPM');

INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','D',TO_DATE('08092005','MMDDYYYY'),32547687,' PPM');

INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('03042005','MMDDYYYY'),32547687,' PPM');

INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','D',TO_DATE('03042005','MMDDYYYY'),21435557,' PPM');

INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('10092005','MMDDYYYY'),35666687,' PPM');

INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('12092005','MMDDYYYY'),35666687,' PPM');

INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','D',TO_DATE('12152005','MMDDYYYY'),68888687,' PPM');

INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('12202006','MMDDYYYY'),35666699,' PPM');

INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','A',TO_DATE('03152005','MMDDYYYY'),22222222,' PPM');

INSERT INTO EMAIL_PRODUCT_HIST
VALUES ('H','D',TO_DATE('05202005','MMDDYYYY'),222222222, 'PPM');

On 8 May, 15:40, "ame...@iwc.net" <ame...@iwc.net> wrote:
> Understood. *In the example below, I would want to ignore customer
> 35666699 because he falls out of the date range, 01/01/2005 -
> 12/31/2005. *I'd ignore customer 21435557 because his status is D. *I
> would include customer 32547687 because his MAX date has a status of
> A. *Likewise, I'd want customer 35666687 since his MAX date has a
> status of A. *I would not want customer 22222222 because his MAX date
> has a status o D.
>
> Hopefully I got it right.......
>
> CREATE TABLE EMAIL_PRODUCT_HIST (
> * ACTION_TYPE * * * VARCHAR2(2),
> * STATUS * * * * * *VARCHAR2(2),
> * ACTION_DATE * * * DATE,
> * EMAIL_HIST_ID * * NUMBER CONSTRAINT EMAIL_PRODUCT_HIST_HIST_ID_NN
> NOT NULL,
> * CUSTOMER_ID * * * NUMBER CONSTRAINT EMAIL_PRODUCT_HIST_CUST_ID_NN
> NOT NULL,
> * EMAIL_PRODUCT_ID *VARCHAR2(5));
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('09032005','MMDDYYYY'),32547687,' PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','D',TO_DATE('08092005','MMDDYYYY'),32547687,' PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('03042005','MMDDYYYY'),32547687,' PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','D',TO_DATE('03042005','MMDDYYYY'),21435557,' PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('10092005','MMDDYYYY'),35666687,' PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('12092005','MMDDYYYY'),35666687,' PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','D',TO_DATE('12152005','MMDDYYYY'),68888687,' PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('12202006','MMDDYYYY'),35666699,' PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','A',TO_DATE('03152005','MMDDYYYY'),22222222,' PPM');
>
> INSERT INTO EMAIL_PRODUCT_HIST
> VALUES ('H','D',TO_DATE('05202005','MMDDYYYY'),222222222, 'PPM');- Hide quoted text -
>
> - Show quoted text -

Almost

CREATE TABLE EMAIL_PRODUCT_HIST (
2 ACTION_TYPE VARCHAR2(2),
3 STATUS VARCHAR2(2),
4 ACTION_DATE DATE,
5 EMAIL_HIST_ID NUMBER CONSTRAINT
EMAIL_PRODUCT_HIST_HIST_ID_NN
6 NOT NULL,
7 CUSTOMER_ID NUMBER CONSTRAINT
EMAIL_PRODUCT_HIST_CUST_ID_NN
8 NOT NULL,
9 EMAIL_PRODUCT_ID VARCHAR2(5));

Table created.

INSERT INTO EMAIL_PRODUCT_HIST
2 VALUES ('H','A',TO_DATE('09032005','MMDDYYYY'),32547687,' PPM');
INSERT INTO EMAIL_PRODUCT_HIST
*
ERROR at line 1:
ORA-00947: not enough values

-g

On May 8, 10:06 am, gazzag <gar...@jamms.org> wrote:
> On 8 May, 15:40, "ame...@iwc.net" <ame...@iwc.net> wrote:
>
>
>
> > Understood. In the example below, I would want to ignore customer
> > 35666699 because he falls out of the date range, 01/01/2005 -
> > 12/31/2005. I'd ignore customer 21435557 because his status is D. I
> > would include customer 32547687 because his MAX date has a status of
> > A. Likewise, I'd want customer 35666687 since his MAX date has a
> > status of A. I would not want customer 22222222 because his MAX date
> > has a status o D.
>
> > Hopefully I got it right.......
>
> > CREATE TABLE EMAIL_PRODUCT_HIST (
> > ACTION_TYPE VARCHAR2(2),
> > STATUS VARCHAR2(2),
> > ACTION_DATE DATE,
> > EMAIL_HIST_ID NUMBER CONSTRAINT EMAIL_PRODUCT_HIST_HIST_ID_NN
> > NOT NULL,
> > CUSTOMER_ID NUMBER CONSTRAINT EMAIL_PRODUCT_HIST_CUST_ID_NN
> > NOT NULL,
> > EMAIL_PRODUCT_ID VARCHAR2(5));
>
> > INSERT INTO EMAIL_PRODUCT_HIST
> > VALUES ('H','A',TO_DATE('09032005','MMDDYYYY'),32547687,' PPM');
>
> > INSERT INTO EMAIL_PRODUCT_HIST
> > VALUES ('H','D',TO_DATE('08092005','MMDDYYYY'),32547687,' PPM');
>
> > INSERT INTO EMAIL_PRODUCT_HIST
> > VALUES ('H','A',TO_DATE('03042005','MMDDYYYY'),32547687,' PPM');
>
> > INSERT INTO EMAIL_PRODUCT_HIST
> > VALUES ('H','D',TO_DATE('03042005','MMDDYYYY'),21435557,' PPM');
>
> > INSERT INTO EMAIL_PRODUCT_HIST
> > VALUES ('H','A',TO_DATE('10092005','MMDDYYYY'),35666687,' PPM');
>
> > INSERT INTO EMAIL_PRODUCT_HIST
> > VALUES ('H','A',TO_DATE('12092005','MMDDYYYY'),35666687,' PPM');
>
> > INSERT INTO EMAIL_PRODUCT_HIST
> > VALUES ('H','D',TO_DATE('12152005','MMDDYYYY'),68888687,' PPM');
>
> > INSERT INTO EMAIL_PRODUCT_HIST
> > VALUES ('H','A',TO_DATE('12202006','MMDDYYYY'),35666699,' PPM');
>
> > INSERT INTO EMAIL_PRODUCT_HIST
> > VALUES ('H','A',TO_DATE('03152005','MMDDYYYY'),22222222,' PPM');
>
> > INSERT INTO EMAIL_PRODUCT_HIST
> > VALUES ('H','D',TO_DATE('05202005','MMDDYYYY'),222222222, 'PPM');- Hide quoted text -
>
> > - Show quoted text -
>
> Almost
>
> CREATE TABLE EMAIL_PRODUCT_HIST (
> 2 ACTION_TYPE VARCHAR2(2),
> 3 STATUS VARCHAR2(2),
> 4 ACTION_DATE DATE,
> 5 EMAIL_HIST_ID NUMBER CONSTRAINT
> EMAIL_PRODUCT_HIST_HIST_ID_NN
> 6 NOT NULL,
> 7 CUSTOMER_ID NUMBER CONSTRAINT
> EMAIL_PRODUCT_HIST_CUST_ID_NN
> 8 NOT NULL,
> 9 EMAIL_PRODUCT_ID VARCHAR2(5));
>
> Table created.
>
> INSERT INTO EMAIL_PRODUCT_HIST
> 2 VALUES ('H','A',TO_DATE('09032005','MMDDYYYY'),32547687,' PPM');
> INSERT INTO EMAIL_PRODUCT_HIST
> *
> ERROR at line 1:
> ORA-00947: not enough values
>
> -g

Crap, forgot that column EMAIL_HIST_ID . You can put anything there,
put 0 there.......