Re: query

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Thu, 08 May 2008 21:40:25 +0200
Message-ID: <fvvkud$545$1@online.de>

amerar_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 Received on Thu May 08 2008 - 14:40:25 CDT