FreeLists / oracle-l / RE: how to generate the output w/o using temp table

["Mark W. Farnham" <mwf@xxxxxxxx>]

First, I'm not sure why 10/2004    1  is not in the result set, and I think
you're trying to deliver an inclusive list of months corresponding to each
start and end date.

Where you have to generate time based lists, I recommend reference tables.
These are not temporary, but permanent reference lists to join against.
Usually you keep a moving window guaranteed by an affirmative business rule
to exceed the endpoints by a healthy margin.

So you need, for example, a months table that is simply a date column filled
in. A hundred years worth would be 1200 rows, so not big. Probably a good
idea to index it. Even days is not very big, and years is really tiny. Weeks
and quarters can be problematic, as can fiscal years, since you have to
insert values based on business defined boundaries rather than the natural
calendar functions.

For natural calendar functions, you just need the single date value,
truncated on creation to the value represented.

select a.id, to_char(b.mymonths,'MM/YYYY') from a, b
where b.mymonths between a.startdate and b.enddate;

Now if you have artificial date groupings, you need the name, starttime, and
endtime (let's say mymonthname, mymonthstart, and mymonthend). Some people,
for example, might end this year's DECEMBER on December 25, if they only
include full Sunday through Saturday weeks within the year.

select a.id, b.mymonthname from a,b
where b.mymonthstart >= a.startdate
  and b.mymonthend   <= a.enddate;

I recommend having all the distinct reference time tables you need to
enumerate by (rather than combining them and having a type column which just
confuses all sorts of issues), but possibly the books Lex mentioned have
other solutions. As far as I know, I invented the method mentioned here, but
probably other folks have also invented it. It is fast and small for
reasonable time quanta. If you start wanting a list of inclusive
microseconds, then you need a different solution unless you have a very,
very small time window.

If you read those books mentioned, please let me know if they have a better
solution.

regards,

mwf

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of mglim2@xxxxxxxxxxxx
Sent: Saturday, July 31, 2004 5:39 AM
To: oracle-l
Subject: how to generate the output w/o using temp table


Hi,

I got a table w/ the ff: data

id   startdate   enddate
1   10/01/2004  12/13/2004
2   12/01/2004  12/02/2005
3   12/03/2004  02/20/2005


need to produce the ff:output

mm/yyy            id
11/2004            1
12/2004            1
12/2004            2
12/2004            3
01/2005            3
02/2005            3

how can i  produce this result w/o using a temporary table in my select
stmt?



Best Regards,
Grace Lim
Suy Sing Comm'l Corp

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------