Groups of Groups?

From: Kevin Arthur <j6vflbl6vy6g8o001_at_sneakemail.com>
Date: 5 Feb 2003 22:13:59 -0800
Message-ID: <e87297f1.0302052213.4bbd8b4f@posting.google.com>

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 Received on Thu Feb 06 2003 - 00:13:59 CST