Re: Help for SQL!?

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 03 Jul 2004 05:54:19 GMT
Message-ID: <pan.2004.07.03.05.54.18.843206@sbcglobal.net>

On Fri, 02 Jul 2004 15:34:52 -0700, JZ wrote:

> Oracle 9.2
>
> I have a table like:
>
> t1 t2 t3
> -----------------------------
> char1 01/01/2004 01/05/2004
> char1 01/10/2004 01/15/2004
> char1 01/20/2004 01/25/2004
> char1 01/01/2004 01/15/2004
> char1 01/01/2004 01/25/2004
> char1 01/10/2004 01/25/2004
>
> t2 value is always less than t3.
>
> How can I use SQL (no PL/SQL) to get output like:
>
> t1 t2 t3
> -----------------------------
> char1 01/01/2004 01/05/2004
> char1 01/10/2004 01/15/2004
> char1 01/20/2004 01/25/2004
>
> WHat I want is: no 'middle' values between t2 and t3 can be found in
> the table.
> The reason why 'char1 01/01/2004 01/15/2004' is no good is because
> '01/10/2004' is between '01/01/2004' and '01/15/2004', and it's in the
> table.
>
> Any help?

That is an old period problem, and it is always resolved by timeline table which contains days and period names. Typically, it's structured like this:

create table timeline (
cdate date not null primary key,

period  varchar2(10) not null,
quarter varchar2(10) not null,
day     varchar2(3)  not null,
month   varchar2(2)  not null,
mm      number(2,0)  not null check (mm between 1 and 12),

holiday varchar2(1) not null check ( holiday in ('Y','N','H')));

column "cdate" contains every day from the chosen "D-Day" onward, 365/366 entries per year. Combined with the period table, which contains period name, with beginning and end of period, you can resolve all of the classic problems: Does day d belong to the period X? To which period does day d belong? How many days from today till the end of period? When is the last Friday in the period X? All of these questions suddenly have trivial answers. For the whole thing to perform well, you would need bitmap indexes on day, month and mm columns.

-- 
Well-behaved women seldom make history

Received on Sat Jul 03 2004 - 00:54:19 CDT