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

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