RE: help with a monthly rollup

  • From: "Brady, Mark" <Mark.Brady@xxxxxxxxxxxxxxxxx>
  • To: <mwf@xxxxxxxx>, <ricks12345@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 14 May 2008 12:01:36 -0400

This article may be of some use.





From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark W. Farnham
Sent: Wednesday, May 14, 2008 10:11 AM
To: ricks12345@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: help with a monthly rollup


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.






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


Since the last day of the month can be


>>> This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the
addressee.  If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2

Other related posts: