Calendar table calculation

Analytics
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!
3 REPLIES

Re: Calendar table calculation

Quick Example with the sys_calendar (Hope it helps):

SELECT
year_of_calendar,
month_of_year
FROM
sys_calendar.calendar
WHERE
calendar_date<=current_date AND
EXTRACT(month FROM calendar_date)<> EXTRACT(MONTH FROM current_date) AND --because you want last 6 months and not current month
calendar_date>current_date-INTERVAL '6' MONTH
GROUP BY 1,2
ORDER BY 1 DESC, 2 DESC;

rgds,
AJ
Enthusiast

Re: Calendar table calculation

It helped me! Thanks AJ.
Enthusiast

Re: Calendar table calculation

how come you are here...