Re: 2 month rolling average/sql*plus

From: Fernando Luna <fern_at_aa.net>
Date: 1995/08/08
Message-ID: <Pine.LNX.3.91.950808112544.32473A-100000_at_big.aa.net>#1/1

Hi Jenny!

On 8 Aug 1995, 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.

This results in an error because you are applying "- 1" to a conversion function "to_char"

A quick and dirty way of doing this is

        select to_char(sysdate, 'MON') from dual; (For the current month)
	select to_char(sysdate -30, 'MON') from dual; (Last Month)
        select to_char(sysdate -60, 'MON') from dual; (Month before that)
        

This way you're subtracting from the system date BEFORE you do the conversion to character data type

Hope that helped...

---

Fernando Luna  	   E-mail:        	fern_at_aa.net
Kent, WA         HomePage: http://www.aa.net/~fern/
             
=========================================================
Boss, n.:
	According to the Oxford English Dictionary, in the Middle Ages

the words "boss" and "botch" were largely synonymous, except that boss, in addition to meaning "a supervisor of workers" also meant "an ornamental stud." Received on Tue Aug 08 1995 - 00:00:00 CEST