Re: 2 month rolling average/sql*plus

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..... Hope this helps.

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