Re: query
From: Urs Metzger <urs_at_ursmetzger.de>
Date: Mon, 12 May 2008 16:57:59 +0200
Message-ID: <g09lsl$lmp$2@online.de>
>> ame..._at_iwc.net schrieb:
>>
>>> On May 8, 1:45 pm, Urs Metzger <u..._at_ursmetzger.de> wrote:
>>>> ame..._at_iwc.net schrieb:
>>>>> 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
>>>>>> 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
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.
Date: Mon, 12 May 2008 16:57:59 +0200
Message-ID: <g09lsl$lmp$2@online.de>
amerar_at_iwc.net schrieb:
> On May 8, 2:40 pm, Urs Metzger <u..._at_ursmetzger.de> wrote:
>> ame..._at_iwc.net schrieb:
>>
>>> On May 8, 1:45 pm, Urs Metzger <u..._at_ursmetzger.de> wrote:
>>>> ame..._at_iwc.net schrieb:
>>>>> 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
>>>>>> 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 Received on Mon May 12 2008 - 09:57:59 CDT