Re: 2 month rolling average/sql*plus

In article <408eq4$f5p_at_inet-nntp-gw-1.us.oracle.com>,

   tkyte_at_us.oracle.com (Thomas J Kyte) wrote:
>farnham_at_spot.Colorado.EDU (Jenny Farnham) wrote:
>
>
>>Howdy ho!
 

>> I would like to do a three month rolling report.
>> I want to get the current months data and the two
>> previous months.
 

>> So for instance,
>> I want all of July and June, and May
>> Next month, all of August, July, June
 

>>How would one do the "date math" for this?
 

>>Select to_char(sysdate,'MON') - 1 from dual;
 

>>results in an error.
 

>>Any clever ideas?
 

>>Jenny
 

>>p.s. I would like to do this in SQL*PLUS.
>> If not, then I will go to PRO*C
>
>
>If you run the following query:
>
>SQL> select add_months( last_day(sysdate),-3)+1, last_day(sysdate) from dual;
>
>ADD_MONTH LAST_DAY(
>--------- ---------
>01-JUN-95 31-AUG-95
>
>You will get the inclusive range for JUN, JUL, and AUG (assuming of course,
 you
>ran this in AUG).
>
>therefore, write your query:
>
>select .....
>from T
>where my_date_field between add_months( last_day(sysdate),-3)+1 and sysdate;
>
>
>Basically, the last_day function takes you to the last day in the current
 month,
>the add_months( x, -3 ) takes you back three months (31-MAY in my example),
 the
>+1 adds one day, bringing to the 1 day of the month two months ago or
>01-JUN.....
>

Not to nit-pick, but this is not quite right either.

If you run this on 2/27/95, you'll get:

LAST_DAY(SYSDATE) = 2/28/95
ADD_MONTHS(2/28/95,-3) = 12/28/94
..+1 = 12/29/94

..not the last day of December. I think you need change the order around:

LAST_DAY(ADD_MONTHS(SYSDATE,-3))+1 Kevin Fleming, Reliable Networx, Inc.
Phoenix, AZ
Internet: kpfhome_at_primenet.com

"Please, don't change colors while I'm talking to you." Received on Sat Aug 12 1995 - 00:00:00 CEST