RE: help with a monthly rollup
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <ricks12345@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 14 May 2008 10:11:13 -0400
There is a pre-defined function for that, but quite often I find that the
business period months that a business wants to use differ from the calendar
months used by the built in functions. So before you build a lot of rollup
queries, you might want to consider storing defined period boundaries by
name. That will add another table to all your joins, but it should be of
little cost (especially if you resolve it as just the boundary time columns
for the single row you need with an inline view or select the boundaries
into PL/SQL variables or other program variables depending on the tools you
are using to build your reports).
That design will also make all your rollup queries directly re-usable for
other rollup periods by simply defining and using a different rollup period
in the table. So your queries might be parameterized with period names such
as '2008M01' for January 2008 and '2008Q01' for the first quarter of 2008.
Regards,
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Rick Ricky
Sent: Tuesday, May 13, 2008 1:45 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: help with a monthly rollup
I need to rollup data by month.
There are a couple ways to do this.
I have a date field. So I can rollup with a to_char(mydate,'YYYYMM')
this requires a where clause as followings
where to_char(mydate,'YYYYMM') = pSomeMonth;
This means I need a function based index to support this. We have alot of
data, so where possible, I want to avoid adding more indexes. Is there a way
to calculate what the last
day of the month is?
so I can use a between as follows
where myDate between to_date(01-MON-YYYY') and
to_date(LASTDAYOFMONTH-MON-YYYY')
Since the last day of the month can be
28,29,30,31
Other related posts:
