query

#61

On May 8, 2:40 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:

Quote:

ame... (AT) iwc (DOT) net schrieb:

On May 8, 1:45 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:

On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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
As your setup script doesn't work, I've created on of my own:

drop table email_product_hist;
drop table customer_account;
create table email_product_hist(Customer_Id number(10,0) not null,
Action_Date date,
Action varchar2(10),
Status varchar2(10));
insert into email_product_hist values(12345678, to_date('12/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(12345678, to_date('03/01/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(12345678, to_date('01/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(24568123, to_date('11/15/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(33445566, to_date('03/01/2006',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(32548798, to_date('02/28/2005',
'MM/DD/YYYY'), 'B', 'Active');
insert into email_product_hist values(77777733, to_date('02/15/2005',
'MM/DD/YYYY'), 'A', 'Inactive');
insert into email_product_hist values(77777733, to_date('02/01/2005',
'MM/DD/YYYY'), 'B', 'Active');

create table customer_account(Customer_Id number(10,0) not null);
commit;

Now, although you don't say so, I guess you want to know for each
customer_id the last status in a given date range with action
code 'A' except for customers in the customer_account table:

Here we go:

SQL> select customer_id,
2 max(status) keep (dense_rank last order by action_date)
3 as status
4 from email_product_hist
5 where action = 'A'
6 and action_date between to_date('01012005','MMDDYYYY')
7 and to_date('03312005','MMDDYYYY')
8 and customer_id not in (select customer_id from customer_account)
9 group by customer_id;

CUSTOMER_ID STATUS
----------- ----------
12345678 Active
77777733 Inactive

As for the count distinct thing I'm completely lost. No idea what you
mean.

Its just a try...

Urs Metzger

Close. They only want customers who's MAX action_date has a status of
A, and the date is within the date range....

I'm lost on how to incorporate the MAX thingy into it......

OK, two more tries:

#1:
All customers except those from customer_account table
whose last status was A
and whose last action date is in a date range:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 group by customer_id)
10 where last_status = 'A'
11 and last_action_date between to_date('01012005','MMDDYYYY')
12 and to_date('03312005','MMDDYYYY');

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05

#2:
All customers except those from customer_account table
whose last status within in a date range was A:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 and action_date between to_date('01012005','MMDDYYYY')
10 and to_date('03312005','MMDDYYYY')
11 group by customer_id)
12 where last_status = 'A';

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05
32547687 A 04.03.05

You see, it's harder to define your requirements precisely then
to code a piece of SQL ;-)

hth,
Urs Metzger

Well, time to turn this over to the customer.....I'll return today and
let you know the results.

Thanks for everyones help!

Also, what is 'KEEP', I've never seen that before.....

#62

On May 8, 2:40 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:

Quote:

ame... (AT) iwc (DOT) net schrieb:

On May 8, 1:45 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:

On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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
As your setup script doesn't work, I've created on of my own:

drop table email_product_hist;
drop table customer_account;
create table email_product_hist(Customer_Id number(10,0) not null,
Action_Date date,
Action varchar2(10),
Status varchar2(10));
insert into email_product_hist values(12345678, to_date('12/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(12345678, to_date('03/01/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(12345678, to_date('01/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(24568123, to_date('11/15/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(33445566, to_date('03/01/2006',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(32548798, to_date('02/28/2005',
'MM/DD/YYYY'), 'B', 'Active');
insert into email_product_hist values(77777733, to_date('02/15/2005',
'MM/DD/YYYY'), 'A', 'Inactive');
insert into email_product_hist values(77777733, to_date('02/01/2005',
'MM/DD/YYYY'), 'B', 'Active');

create table customer_account(Customer_Id number(10,0) not null);
commit;

Now, although you don't say so, I guess you want to know for each
customer_id the last status in a given date range with action
code 'A' except for customers in the customer_account table:

Here we go:

SQL> select customer_id,
2 max(status) keep (dense_rank last order by action_date)
3 as status
4 from email_product_hist
5 where action = 'A'
6 and action_date between to_date('01012005','MMDDYYYY')
7 and to_date('03312005','MMDDYYYY')
8 and customer_id not in (select customer_id from customer_account)
9 group by customer_id;

CUSTOMER_ID STATUS
----------- ----------
12345678 Active
77777733 Inactive

As for the count distinct thing I'm completely lost. No idea what you
mean.

Its just a try...

Urs Metzger

Close. They only want customers who's MAX action_date has a status of
A, and the date is within the date range....

I'm lost on how to incorporate the MAX thingy into it......

OK, two more tries:

#1:
All customers except those from customer_account table
whose last status was A
and whose last action date is in a date range:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 group by customer_id)
10 where last_status = 'A'
11 and last_action_date between to_date('01012005','MMDDYYYY')
12 and to_date('03312005','MMDDYYYY');

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05

#2:
All customers except those from customer_account table
whose last status within in a date range was A:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 and action_date between to_date('01012005','MMDDYYYY')
10 and to_date('03312005','MMDDYYYY')
11 group by customer_id)
12 where last_status = 'A';

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05
32547687 A 04.03.05

You see, it's harder to define your requirements precisely then
to code a piece of SQL ;-)

hth,
Urs Metzger

I have a question, look at your query vs. the other one:

select * from (
select customer_id,
max(action) keep (dense_rank last order by action_date
desc)
as last_status,
max(action_date) as last_action_date
from email_product_hist
where customer_id not in (select customer_id
from customer_account)
group by customer_id)
where last_status = 'A'
and last_action_date between to_date('01012007','MMDDYYYY')
and to_date('03312007','MMDDYYYY');

---> 16,347

SELECT count(distinct(customer_id))
from email_product_hist e
WHERE action_date between to_date('01012007','MMDDYYYY') and
to_date('03312007','MMDDYYYY')
and e.action = 'A'
and e.customer_id NOT IN (
select customer_id from customer_account);

--> 11,204

Why the difference? I'm just doing a count of customer_id's where
they are NOT in the customer_account table and have the date range and
the status......supposedly the same as yours, just without any MAX
date functions.....

#63

On May 8, 2:40 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:

Quote:

ame... (AT) iwc (DOT) net schrieb:

On May 8, 1:45 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:

On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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
As your setup script doesn't work, I've created on of my own:

drop table email_product_hist;
drop table customer_account;
create table email_product_hist(Customer_Id number(10,0) not null,
Action_Date date,
Action varchar2(10),
Status varchar2(10));
insert into email_product_hist values(12345678, to_date('12/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(12345678, to_date('03/01/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(12345678, to_date('01/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(24568123, to_date('11/15/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(33445566, to_date('03/01/2006',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(32548798, to_date('02/28/2005',
'MM/DD/YYYY'), 'B', 'Active');
insert into email_product_hist values(77777733, to_date('02/15/2005',
'MM/DD/YYYY'), 'A', 'Inactive');
insert into email_product_hist values(77777733, to_date('02/01/2005',
'MM/DD/YYYY'), 'B', 'Active');

create table customer_account(Customer_Id number(10,0) not null);
commit;

Now, although you don't say so, I guess you want to know for each
customer_id the last status in a given date range with action
code 'A' except for customers in the customer_account table:

Here we go:

SQL> select customer_id,
2 max(status) keep (dense_rank last order by action_date)
3 as status
4 from email_product_hist
5 where action = 'A'
6 and action_date between to_date('01012005','MMDDYYYY')
7 and to_date('03312005','MMDDYYYY')
8 and customer_id not in (select customer_id from customer_account)
9 group by customer_id;

CUSTOMER_ID STATUS
----------- ----------
12345678 Active
77777733 Inactive

As for the count distinct thing I'm completely lost. No idea what you
mean.

Its just a try...

Urs Metzger

Close. They only want customers who's MAX action_date has a status of
A, and the date is within the date range....

I'm lost on how to incorporate the MAX thingy into it......

OK, two more tries:

#1:
All customers except those from customer_account table
whose last status was A
and whose last action date is in a date range:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 group by customer_id)
10 where last_status = 'A'
11 and last_action_date between to_date('01012005','MMDDYYYY')
12 and to_date('03312005','MMDDYYYY');

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05

#2:
All customers except those from customer_account table
whose last status within in a date range was A:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 and action_date between to_date('01012005','MMDDYYYY')
10 and to_date('03312005','MMDDYYYY')
11 group by customer_id)
12 where last_status = 'A';

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05
32547687 A 04.03.05

You see, it's harder to define your requirements precisely then
to code a piece of SQL ;-)

hth,
Urs Metzger

I have a question, look at your query vs. the other one:

select * from (
select customer_id,
max(action) keep (dense_rank last order by action_date
desc)
as last_status,
max(action_date) as last_action_date
from email_product_hist
where customer_id not in (select customer_id
from customer_account)
group by customer_id)
where last_status = 'A'
and last_action_date between to_date('01012007','MMDDYYYY')
and to_date('03312007','MMDDYYYY');

---> 16,347

SELECT count(distinct(customer_id))
from email_product_hist e
WHERE action_date between to_date('01012007','MMDDYYYY') and
to_date('03312007','MMDDYYYY')
and e.action = 'A'
and e.customer_id NOT IN (
select customer_id from customer_account);

--> 11,204

Why the difference? I'm just doing a count of customer_id's where
they are NOT in the customer_account table and have the date range and
the status......supposedly the same as yours, just without any MAX
date functions.....

#64

On May 8, 2:40 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:

Quote:

ame... (AT) iwc (DOT) net schrieb:

On May 8, 1:45 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:

On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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
As your setup script doesn't work, I've created on of my own:

drop table email_product_hist;
drop table customer_account;
create table email_product_hist(Customer_Id number(10,0) not null,
Action_Date date,
Action varchar2(10),
Status varchar2(10));
insert into email_product_hist values(12345678, to_date('12/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(12345678, to_date('03/01/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(12345678, to_date('01/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(24568123, to_date('11/15/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(33445566, to_date('03/01/2006',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(32548798, to_date('02/28/2005',
'MM/DD/YYYY'), 'B', 'Active');
insert into email_product_hist values(77777733, to_date('02/15/2005',
'MM/DD/YYYY'), 'A', 'Inactive');
insert into email_product_hist values(77777733, to_date('02/01/2005',
'MM/DD/YYYY'), 'B', 'Active');

create table customer_account(Customer_Id number(10,0) not null);
commit;

Now, although you don't say so, I guess you want to know for each
customer_id the last status in a given date range with action
code 'A' except for customers in the customer_account table:

Here we go:

SQL> select customer_id,
2 max(status) keep (dense_rank last order by action_date)
3 as status
4 from email_product_hist
5 where action = 'A'
6 and action_date between to_date('01012005','MMDDYYYY')
7 and to_date('03312005','MMDDYYYY')
8 and customer_id not in (select customer_id from customer_account)
9 group by customer_id;

CUSTOMER_ID STATUS
----------- ----------
12345678 Active
77777733 Inactive

As for the count distinct thing I'm completely lost. No idea what you
mean.

Its just a try...

Urs Metzger

Close. They only want customers who's MAX action_date has a status of
A, and the date is within the date range....

I'm lost on how to incorporate the MAX thingy into it......

OK, two more tries:

#1:
All customers except those from customer_account table
whose last status was A
and whose last action date is in a date range:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 group by customer_id)
10 where last_status = 'A'
11 and last_action_date between to_date('01012005','MMDDYYYY')
12 and to_date('03312005','MMDDYYYY');

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05

#2:
All customers except those from customer_account table
whose last status within in a date range was A:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 and action_date between to_date('01012005','MMDDYYYY')
10 and to_date('03312005','MMDDYYYY')
11 group by customer_id)
12 where last_status = 'A';

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05
32547687 A 04.03.05

You see, it's harder to define your requirements precisely then
to code a piece of SQL ;-)

hth,
Urs Metzger

I have a question, look at your query vs. the other one:

select * from (
select customer_id,
max(action) keep (dense_rank last order by action_date
desc)
as last_status,
max(action_date) as last_action_date
from email_product_hist
where customer_id not in (select customer_id
from customer_account)
group by customer_id)
where last_status = 'A'
and last_action_date between to_date('01012007','MMDDYYYY')
and to_date('03312007','MMDDYYYY');

---> 16,347

SELECT count(distinct(customer_id))
from email_product_hist e
WHERE action_date between to_date('01012007','MMDDYYYY') and
to_date('03312007','MMDDYYYY')
and e.action = 'A'
and e.customer_id NOT IN (
select customer_id from customer_account);

--> 11,204

Why the difference? I'm just doing a count of customer_id's where
they are NOT in the customer_account table and have the date range and
the status......supposedly the same as yours, just without any MAX
date functions.....

#65

On May 8, 2:40 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:

Quote:

ame... (AT) iwc (DOT) net schrieb:

On May 8, 1:45 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:

On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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
As your setup script doesn't work, I've created on of my own:

drop table email_product_hist;
drop table customer_account;
create table email_product_hist(Customer_Id number(10,0) not null,
Action_Date date,
Action varchar2(10),
Status varchar2(10));
insert into email_product_hist values(12345678, to_date('12/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(12345678, to_date('03/01/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(12345678, to_date('01/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(24568123, to_date('11/15/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(33445566, to_date('03/01/2006',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(32548798, to_date('02/28/2005',
'MM/DD/YYYY'), 'B', 'Active');
insert into email_product_hist values(77777733, to_date('02/15/2005',
'MM/DD/YYYY'), 'A', 'Inactive');
insert into email_product_hist values(77777733, to_date('02/01/2005',
'MM/DD/YYYY'), 'B', 'Active');

create table customer_account(Customer_Id number(10,0) not null);
commit;

Now, although you don't say so, I guess you want to know for each
customer_id the last status in a given date range with action
code 'A' except for customers in the customer_account table:

Here we go:

SQL> select customer_id,
2 max(status) keep (dense_rank last order by action_date)
3 as status
4 from email_product_hist
5 where action = 'A'
6 and action_date between to_date('01012005','MMDDYYYY')
7 and to_date('03312005','MMDDYYYY')
8 and customer_id not in (select customer_id from customer_account)
9 group by customer_id;

CUSTOMER_ID STATUS
----------- ----------
12345678 Active
77777733 Inactive

As for the count distinct thing I'm completely lost. No idea what you
mean.

Its just a try...

Urs Metzger

Close. They only want customers who's MAX action_date has a status of
A, and the date is within the date range....

I'm lost on how to incorporate the MAX thingy into it......

OK, two more tries:

#1:
All customers except those from customer_account table
whose last status was A
and whose last action date is in a date range:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 group by customer_id)
10 where last_status = 'A'
11 and last_action_date between to_date('01012005','MMDDYYYY')
12 and to_date('03312005','MMDDYYYY');

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05

#2:
All customers except those from customer_account table
whose last status within in a date range was A:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 and action_date between to_date('01012005','MMDDYYYY')
10 and to_date('03312005','MMDDYYYY')
11 group by customer_id)
12 where last_status = 'A';

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05
32547687 A 04.03.05

You see, it's harder to define your requirements precisely then
to code a piece of SQL ;-)

hth,
Urs Metzger

I have a question, look at your query vs. the other one:

select * from (
select customer_id,
max(action) keep (dense_rank last order by action_date
desc)
as last_status,
max(action_date) as last_action_date
from email_product_hist
where customer_id not in (select customer_id
from customer_account)
group by customer_id)
where last_status = 'A'
and last_action_date between to_date('01012007','MMDDYYYY')
and to_date('03312007','MMDDYYYY');

---> 16,347

SELECT count(distinct(customer_id))
from email_product_hist e
WHERE action_date between to_date('01012007','MMDDYYYY') and
to_date('03312007','MMDDYYYY')
and e.action = 'A'
and e.customer_id NOT IN (
select customer_id from customer_account);

--> 11,204

Why the difference? I'm just doing a count of customer_id's where
they are NOT in the customer_account table and have the date range and
the status......supposedly the same as yours, just without any MAX
date functions.....

#66

amerar (AT) iwc (DOT) net schrieb:

Quote:

On May 8, 2:40 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:

On May 8, 1:45 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:
On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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
As your setup script doesn't work, I've created on of my own:
drop table email_product_hist;
drop table customer_account;
create table email_product_hist(Customer_Id number(10,0) not null,
Action_Date date,
Action varchar2(10),
Status varchar2(10));
insert into email_product_hist values(12345678, to_date('12/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(12345678, to_date('03/01/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(12345678, to_date('01/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(24568123, to_date('11/15/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(33445566, to_date('03/01/2006',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(32548798, to_date('02/28/2005',
'MM/DD/YYYY'), 'B', 'Active');
insert into email_product_hist values(77777733, to_date('02/15/2005',
'MM/DD/YYYY'), 'A', 'Inactive');
insert into email_product_hist values(77777733, to_date('02/01/2005',
'MM/DD/YYYY'), 'B', 'Active');
create table customer_account(Customer_Id number(10,0) not null);
commit;
Now, although you don't say so, I guess you want to know for each
customer_id the last status in a given date range with action
code 'A' except for customers in the customer_account table:
Here we go:
SQL> select customer_id,
2 max(status) keep (dense_rank last order by action_date)
3 as status
4 from email_product_hist
5 where action = 'A'
6 and action_date between to_date('01012005','MMDDYYYY')
7 and to_date('03312005','MMDDYYYY')
8 and customer_id not in (select customer_id from customer_account)
9 group by customer_id;
CUSTOMER_ID STATUS
----------- ----------
12345678 Active
77777733 Inactive
As for the count distinct thing I'm completely lost. No idea what you
mean.
Its just a try...
Urs Metzger
Close. They only want customers who's MAX action_date has a status of
A, and the date is within the date range....
I'm lost on how to incorporate the MAX thingy into it......
OK, two more tries:

#1:
All customers except those from customer_account table
whose last status was A
and whose last action date is in a date range:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 group by customer_id)
10 where last_status = 'A'
11 and last_action_date between to_date('01012005','MMDDYYYY')
12 and to_date('03312005','MMDDYYYY');

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05

#2:
All customers except those from customer_account table
whose last status within in a date range was A:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 and action_date between to_date('01012005','MMDDYYYY')
10 and to_date('03312005','MMDDYYYY')
11 group by customer_id)
12 where last_status = 'A';

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05
32547687 A 04.03.05

You see, it's harder to define your requirements precisely then
to code a piece of SQL ;-)

hth,
Urs Metzger

Well, time to turn this over to the customer.....I'll return today and
let you know the results.

Thanks for everyones help!

Also, what is 'KEEP', I've never seen that before.....

max(status) keep (dense_rank last order by action_date) means:

find the row with the maximum (i.e. last) action_date and return
its status.

It came with Oracle 9i (iirc).

Urs Metzger

#67

amerar (AT) iwc (DOT) net schrieb:

Quote:

On May 8, 2:40 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:

On May 8, 1:45 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:
On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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
As your setup script doesn't work, I've created on of my own:
drop table email_product_hist;
drop table customer_account;
create table email_product_hist(Customer_Id number(10,0) not null,
Action_Date date,
Action varchar2(10),
Status varchar2(10));
insert into email_product_hist values(12345678, to_date('12/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(12345678, to_date('03/01/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(12345678, to_date('01/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(24568123, to_date('11/15/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(33445566, to_date('03/01/2006',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(32548798, to_date('02/28/2005',
'MM/DD/YYYY'), 'B', 'Active');
insert into email_product_hist values(77777733, to_date('02/15/2005',
'MM/DD/YYYY'), 'A', 'Inactive');
insert into email_product_hist values(77777733, to_date('02/01/2005',
'MM/DD/YYYY'), 'B', 'Active');
create table customer_account(Customer_Id number(10,0) not null);
commit;
Now, although you don't say so, I guess you want to know for each
customer_id the last status in a given date range with action
code 'A' except for customers in the customer_account table:
Here we go:
SQL> select customer_id,
2 max(status) keep (dense_rank last order by action_date)
3 as status
4 from email_product_hist
5 where action = 'A'
6 and action_date between to_date('01012005','MMDDYYYY')
7 and to_date('03312005','MMDDYYYY')
8 and customer_id not in (select customer_id from customer_account)
9 group by customer_id;
CUSTOMER_ID STATUS
----------- ----------
12345678 Active
77777733 Inactive
As for the count distinct thing I'm completely lost. No idea what you
mean.
Its just a try...
Urs Metzger
Close. They only want customers who's MAX action_date has a status of
A, and the date is within the date range....
I'm lost on how to incorporate the MAX thingy into it......
OK, two more tries:

#1:
All customers except those from customer_account table
whose last status was A
and whose last action date is in a date range:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 group by customer_id)
10 where last_status = 'A'
11 and last_action_date between to_date('01012005','MMDDYYYY')
12 and to_date('03312005','MMDDYYYY');

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05

#2:
All customers except those from customer_account table
whose last status within in a date range was A:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 and action_date between to_date('01012005','MMDDYYYY')
10 and to_date('03312005','MMDDYYYY')
11 group by customer_id)
12 where last_status = 'A';

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05
32547687 A 04.03.05

You see, it's harder to define your requirements precisely then
to code a piece of SQL ;-)

hth,
Urs Metzger

Well, time to turn this over to the customer.....I'll return today and
let you know the results.

Thanks for everyones help!

Also, what is 'KEEP', I've never seen that before.....

max(status) keep (dense_rank last order by action_date) means:

find the row with the maximum (i.e. last) action_date and return
its status.

It came with Oracle 9i (iirc).

Urs Metzger

#68

amerar (AT) iwc (DOT) net schrieb:

Quote:

On May 8, 2:40 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:

On May 8, 1:45 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:
On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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
As your setup script doesn't work, I've created on of my own:
drop table email_product_hist;
drop table customer_account;
create table email_product_hist(Customer_Id number(10,0) not null,
Action_Date date,
Action varchar2(10),
Status varchar2(10));
insert into email_product_hist values(12345678, to_date('12/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(12345678, to_date('03/01/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(12345678, to_date('01/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(24568123, to_date('11/15/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(33445566, to_date('03/01/2006',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(32548798, to_date('02/28/2005',
'MM/DD/YYYY'), 'B', 'Active');
insert into email_product_hist values(77777733, to_date('02/15/2005',
'MM/DD/YYYY'), 'A', 'Inactive');
insert into email_product_hist values(77777733, to_date('02/01/2005',
'MM/DD/YYYY'), 'B', 'Active');
create table customer_account(Customer_Id number(10,0) not null);
commit;
Now, although you don't say so, I guess you want to know for each
customer_id the last status in a given date range with action
code 'A' except for customers in the customer_account table:
Here we go:
SQL> select customer_id,
2 max(status) keep (dense_rank last order by action_date)
3 as status
4 from email_product_hist
5 where action = 'A'
6 and action_date between to_date('01012005','MMDDYYYY')
7 and to_date('03312005','MMDDYYYY')
8 and customer_id not in (select customer_id from customer_account)
9 group by customer_id;
CUSTOMER_ID STATUS
----------- ----------
12345678 Active
77777733 Inactive
As for the count distinct thing I'm completely lost. No idea what you
mean.
Its just a try...
Urs Metzger
Close. They only want customers who's MAX action_date has a status of
A, and the date is within the date range....
I'm lost on how to incorporate the MAX thingy into it......
OK, two more tries:

#1:
All customers except those from customer_account table
whose last status was A
and whose last action date is in a date range:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 group by customer_id)
10 where last_status = 'A'
11 and last_action_date between to_date('01012005','MMDDYYYY')
12 and to_date('03312005','MMDDYYYY');

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05

#2:
All customers except those from customer_account table
whose last status within in a date range was A:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 and action_date between to_date('01012005','MMDDYYYY')
10 and to_date('03312005','MMDDYYYY')
11 group by customer_id)
12 where last_status = 'A';

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05
32547687 A 04.03.05

You see, it's harder to define your requirements precisely then
to code a piece of SQL ;-)

hth,
Urs Metzger

Well, time to turn this over to the customer.....I'll return today and
let you know the results.

Thanks for everyones help!

Also, what is 'KEEP', I've never seen that before.....

max(status) keep (dense_rank last order by action_date) means:

find the row with the maximum (i.e. last) action_date and return
its status.

It came with Oracle 9i (iirc).

Urs Metzger

#69

amerar (AT) iwc (DOT) net schrieb:

Quote:

On May 8, 2:40 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:

On May 8, 1:45 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote:
ame... (AT) iwc (DOT) net schrieb:
On May 7, 11:44 am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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
As your setup script doesn't work, I've created on of my own:
drop table email_product_hist;
drop table customer_account;
create table email_product_hist(Customer_Id number(10,0) not null,
Action_Date date,
Action varchar2(10),
Status varchar2(10));
insert into email_product_hist values(12345678, to_date('12/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(12345678, to_date('03/01/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(12345678, to_date('01/01/2005',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(24568123, to_date('11/15/2005',
'MM/DD/YYYY'), 'B', 'Inactive');
insert into email_product_hist values(33445566, to_date('03/01/2006',
'MM/DD/YYYY'), 'A', 'Active');
insert into email_product_hist values(32548798, to_date('02/28/2005',
'MM/DD/YYYY'), 'B', 'Active');
insert into email_product_hist values(77777733, to_date('02/15/2005',
'MM/DD/YYYY'), 'A', 'Inactive');
insert into email_product_hist values(77777733, to_date('02/01/2005',
'MM/DD/YYYY'), 'B', 'Active');
create table customer_account(Customer_Id number(10,0) not null);
commit;
Now, although you don't say so, I guess you want to know for each
customer_id the last status in a given date range with action
code 'A' except for customers in the customer_account table:
Here we go:
SQL> select customer_id,
2 max(status) keep (dense_rank last order by action_date)
3 as status
4 from email_product_hist
5 where action = 'A'
6 and action_date between to_date('01012005','MMDDYYYY')
7 and to_date('03312005','MMDDYYYY')
8 and customer_id not in (select customer_id from customer_account)
9 group by customer_id;
CUSTOMER_ID STATUS
----------- ----------
12345678 Active
77777733 Inactive
As for the count distinct thing I'm completely lost. No idea what you
mean.
Its just a try...
Urs Metzger
Close. They only want customers who's MAX action_date has a status of
A, and the date is within the date range....
I'm lost on how to incorporate the MAX thingy into it......
OK, two more tries:

#1:
All customers except those from customer_account table
whose last status was A
and whose last action date is in a date range:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 group by customer_id)
10 where last_status = 'A'
11 and last_action_date between to_date('01012005','MMDDYYYY')
12 and to_date('03312005','MMDDYYYY');

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05

#2:
All customers except those from customer_account table
whose last status within in a date range was A:

SQL> select * from (
2 select customer_id,
3 max(status) keep (dense_rank last order by action_date)
4 as last_status,
5 max(action_date) as last_action_date
6 from email_product_hist
7 where customer_id not in (select customer_id
8 from customer_account)
9 and action_date between to_date('01012005','MMDDYYYY')
10 and to_date('03312005','MMDDYYYY')
11 group by customer_id)
12 where last_status = 'A';

CUSTOMER_ID LA LAST_ACT
----------- -- --------
22222222 A 15.03.05
32547687 A 04.03.05

You see, it's harder to define your requirements precisely then
to code a piece of SQL ;-)

hth,
Urs Metzger

Well, time to turn this over to the customer.....I'll return today and
let you know the results.

Thanks for everyones help!

Also, what is 'KEEP', I've never seen that before.....

max(status) keep (dense_rank last order by action_date) means:

find the row with the maximum (i.e. last) action_date and return
its status.

It came with Oracle 9i (iirc).

Urs Metzger