Re: query

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Thu, 08 May 2008 20:45:15 +0200
Message-ID: <fvvhmv$118$1@online.de>

amerar_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 Received on Thu May 08 2008 - 13:45:15 CDT