ROLLING 12 MONTHS

Database
Enthusiast

ROLLING 12 MONTHS

Hi.

I need the syntax to create a table or view for 12rolling months

using the system calendar (syscal).

using Teradata 13.11

Thanks

7 REPLIES
Enthusiast

Re: ROLLING 12 MONTHS

select extract(year from calendar_date) yyyy, extract(month from calendar_date) mm

 from sys_calendar.calendar

where calendar_date between add_months(date,-11) and date

group by 1,2

order by 1,2

Enthusiast

Re: ROLLING 12 MONTHS

thanks Patel.

Im not sure if this will work however.

I need to show the first day of the month 12 months ago to the first of day of the current month and I need these dates to drop and add and the beginning of a new month.

Junior Contributor

Re: ROLLING 12 MONTHS

It would have been easier if you added your definition of "rolling 12 months" in the initial post.

WHERE calendar_date BETWEEN ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE)-1), -12) 
AND CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE)-1)
Enthusiast

Re: ROLLING 12 MONTHS

my apologies.

Thanks much.

Enthusiast

Re: ROLLING 12 MONTHS

trying to format it as a 'start_date' and 'end_date'

where the start_date and end_date changes every

month

Junior Contributor

Re: ROLLING 12 MONTHS

I don't understand what you mean.

Using CURRENT_DATE automatically changes the result every month.

Enthusiast

Re: ROLLING 12 MONTHS

select

 min(calendar_date) start_date,max(calendar_date) end_date

 from sys_calendar.calendar

where

 calendar_date BETWEEN ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE)-1), -12)

                                   AND CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE)-1)

group by year_of_calendar, month_of_year

order by 1

You may have to manipulate current month