Re: help with a monthly rollup

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_at_constellation.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Mark W. Farnham
> *Sent:* Wednesday, May 14, 2008 10:11 AM
> *To:* ricks12345_at_gmail.com; oracle-l_at_freelists.org
> *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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Rick Ricky
> *Sent:* Tuesday, May 13, 2008 1:45 PM
> *To:* oracle-l_at_freelists.org
> *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

--
http://www.freelists.org/webpage/oracle-l