Re: Groups of Groups?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 6 Feb 2003 18:17:44 GMT
Message-ID: <b1u8s7$166n38$1@ID-82536.news.dfncis.de>

Hello

Your wording is a little confusing, at least to me, so I am not sure if I have understood what exactly you need. Anyway, here's what I think should help you:

set feedback off
set lines 150
set pages 5000

alter session set nls_date_format = 'dd-mon-yyyy';

create table prefix_ (
  company_id char(3),
  prefix varchar2(10),
  start_date date,
  end_date date,
  id number
);

insert into prefix_ values (
'ABC','12345','23-MAR-1989','06-JUN-2099', 36 ); insert into prefix_ values (
'ABC','12345','11-NOV-1994','06-JUN-2099', 0 );

• This is the record with the lastest date that
• matches insert into prefix_ values ( 'ABC','123450','18-FEB-1986','06-JUN-2099', 1 );
• this is the record with the longest prefix
• that matches insert into prefix_ values ( 'ABC','1234501','01-JAN-1993','06-JUN-2099', 234);

insert into prefix_ values (
'ABC','123451','28-AUG-1970','06-JUN-2099', 5); insert into prefix_ values (
'ABC','123452','21-MAR-1975','06-JUN-2099', 6); insert into prefix_ values (
'ABC','1234594','26-FEB-1974','06-JUN-2099', 7); select id, start_date from (
  select
    id,
    max(start_date) over() start_date,
    rank() over (order by length(prefix) desc, start_date desc) r   from
    prefix_
  where
    sysdate between start_date and end_date and     substr('1234501234',1,length(prefix)) = prefix   )
where r = 1;

drop table prefix_;

OUTPUT ===>         ID START_DATE
---------- -----------

       234 11-nov-1994

hth

Rene Nyffenegger

> I've got a table that looks like this:
>
> COMPANY_ID NOT NULL CHAR(3)
> PHONE_NUMBER_PREFIX NOT NULL VARCHAR2(16)
> START_DATE NOT NULL DATE
> END_DATE NOT NULL DATE
> LOCATION_ID NOT NULL NUMBER(8)
> [...]
>
> Table can contain data like this:
>
> COM PHONE_NUMBER_PRE START_DATE END_DATE LOCATION_ID
> --- ---------------- ----------- ----------- -----------
> ABC 12345 23-MAR-1989 06-JUN-2099 36
> ABC 12345 11-NOV-1994 06-JUN-2099 0
> ABC 123450 18-FEB-1986 06-JUN-2099 1
> ABC 123450 01-JAN-2003 06-JUN-2099 234
> ABC 123451 ...
> ABC 123452 ...
> ABC 1234594 ...
>
>
> Note multiple prefixes for multiple date ranges.
>
> Given a particular phone number, I need it to match on the longest
> matching prefix available. Note that every possible pattern is not
> present; only specific ones.
>
> I also needs to get the record with the latest date range for that
> particular prefix, where the date of the call to that number fits
> within that date range.
>
> What I'm after is the "location_id" which will be different for each
> entry.
>
> So in the above example, phone number "123-450-1234" with a call date
> of February 1, 2003 should match on:
>
> ABC 123450 01-JAN-2003 06-JUN-2099 234
>
> since it is a more specific match than just '12345'.
>
> I'm afraid I'm going in circles. I can do (an admittedly not pretty)
> query using max() to group on "phone_number_prefix" and get the
> 'largest' prefix.
>
> Or, I can do same on "start_date" go get the latest start date.
>
> But, how to the the combination of both?
>
> If you do both max group functions in one query, you combine values
> from multiple rows. Not the desired result.
>
> I am attempting to use just straight SQL. Maybe a PL/SQL block is the
> way to go, but I was just sure I could do it through regular SQL.
> (Stubborn, I guess)
>
> I feel like I need a series of nested sub-queries, but not sure how to
> go about it, since one I group things (prefix or date), I have nothing
> left in common.
>
> Any help would be much appreciated!
>
>
> For what it's worth, here's a query re getting the max prefix:
>
> select
> o.phone_number,
> max(p.phone_number_prefix)
> -- max(p.start_date) --no,no,no!
> from
> originating_table o,
> phone_prefix_table p
> where
> (p.company_id = o.company_id)
> AND (substr(o.phone_number,1,length(p.phone_number_prefix))
> = p.phone_number_prefix)
> AND (o.call_date BETWEEN p.start_date AND p.end_date)
> group by o.phone_number;
>
>
> Thanks!
>
> Kevin Arthur

-- 
  no sig today

Received on Thu Feb 06 2003 - 12:17:44 CST