RE: how to generate the output w/o using temp table
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Aug 2004 16:28:40 -0400
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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
