Calendar table calculation

Database
Enthusiast

Calendar table calculation

Hi All,
I am a newbi to this forum so can someone help me out.

I am trying to get past 6 month/year numbers from a calendar table in TD.

For example I can query the calendar table on the current month and it will give me this….
Yr Mo
2010 01

Now how can I write a query so that
Results set should look like this:

Yr Mo
2009 12
2009 11
2009 10
2009 09
2009 08
2009 07

It should be a rolling looking back 6 months, such as when current month is:
Yr Mo
2010 04

Results set should look like this:

Yr Mo
2010 03
2010 02
2010 01
2009 12
2009 11
2009 10

Can anyone please help!

Thanks!
1 REPLY
Enthusiast

Re: Calendar table calculation

Hi Ratu,

Please try below query. Hope this helps.

select al1.year_of_calendar, al1.month_of_year
from
sys_calendar.CALENDAR al1,
(select month_of_calendar from sys_calendar.CALENDAR where calendar_date = current_date) al2
where
al1.calendar_date < current_date
and al1.month_of_calendar between al2.month_of_calendar-6 and al2.month_of_calendar-1
group by 1,2
order by 1,2