Re: 2 month rolling average/sql*plus

On Sat, 12 Aug 95 01:59:01 GMT, you wrote:

>In article <408eq4$f5p_at_inet-nntp-gw-1.us.oracle.com>,
> tkyte_at_us.oracle.com (Thomas J Kyte) wrote:
>>
>>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 to nit-pick back either, but actually it was quite right. This is from my sql*plus session:  

SQL> select last_day( '27-feb-95' ) from dual;  

LAST_DAY(

28-FEB-95
^^^^^^^^^  Agrees with you..

 

SQL> select add_months( last_day( '27-feb-95' ), -3 ) from dual;  

ADD_MONTH

30-NOV-94
^^^^^^^^^  Does not agree with you (doesn't go to the 28'th of NOV).

 

SQL> select add_months( last_day( '27-feb-95' ), -3 ) + 1 from dual;  

ADD_MONTH

01-DEC-94
^^^^^^^^^ Is the answer you are looking for.
 

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

>LAST_DAY(ADD_MONTHS(SYSDATE,-3))+1
If you look at the definition of ADD_MONTHS, you will see why this works. Add_months(D,N), WHEN D is the LAST DAY of the month, always returns the LAST DAY of the resulting month.

<quote> -- SQL Language Reference manual

 ADD_MONTHS Syntax ADD_MONTHS(d,n)

Purpose Returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d .

</quote>

That is NOT to say the switching them around would not work all the time (so the above formula will work as well). Going back three months and going to the last day and then adding one always will result in the same result.

>Kevin Fleming, Reliable Networx, Inc.
>Phoenix, AZ
>Internet: kpfhome_at_primenet.com

>"Please, don't change colors while I'm talking to you."

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Sat Aug 12 1995 - 00:00:00 CEST