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