| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: how to generate the output w/o using temp table
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of mglim2_at_softhome.net
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
 |
 |