This book by Kimball has comes with a CD that contains an Excel file with dates to be used for rollup in many different formats. Weekend, Month, quarter, work days, etc. It is easily translatable to a table in Oracle. http://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247 Probably also in this book: http://www.amazon.com/Data-Warehouse-Lifecycle-Toolkit/dp/0470149779 On Wed, May 14, 2008 at 9:01 AM, Brady, Mark <Mark.Brady@xxxxxxxxxxxxxxxxx> wrote: > *This article may be of some use.* > > * * > > www.r*kimball*.com/html/designtipsPDF/*Kimball*DT51LatestThinking.pdf<http://www.rkimball.com/html/designtipsPDF/KimballDT51LatestThinking.pdf> > > > > * * > > * * > ------------------------------ > > *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. > > > > 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 > > >>> 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 > > -- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist