Re: help with a monthly rollup

From: <TESTAJ3_at_nationwide.com>
Date: Tue, 13 May 2008 13:54:54 -0400
Message-ID: <OFCA02CBCE.31449167-ON85257448.00623CA2-85257448.006268B8@lnotes-gw.ent.nwie.net>


i always did the last day of the month as the first day of a particular month - 1, that way leap year was taken into account automagically or you can use: LAST_DAY(date)
LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the datatype of date.

joe



You can have it: Fast, Right or Cheap, pick 2 of the 3. Fast + Right is Expensive
Fast + Cheap will be incorrect.
Right + Cheap will take a while.

"Rick Ricky" <ricks12345_at_gmail.com>
 

Sent by: oracle-l-bounce_at_freelists.org

05/13/2008 01:44 PM
Please respond to ricks12345_at_gmail.com

From
"Rick Ricky" <ricks12345_at_gmail.com>

To
oracle-l_at_freelists.org
cc

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
Received on Tue May 13 2008 - 12:54:54 CDT

Original text of this message