All Forums Database
cmyeyez 11 posts Joined 02/13
21 Aug 2014
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

PatelSatish 4 posts Joined 02/12
21 Aug 2014

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
 

cmyeyez 11 posts Joined 02/13
22 Aug 2014

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.
 

dnoeth 4628 posts Joined 11/04
22 Aug 2014

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)

 

Dieter

cmyeyez 11 posts Joined 02/13
26 Aug 2014

my apologies.
Thanks much.

cmyeyez 11 posts Joined 02/13
26 Aug 2014

trying to format it as a 'start_date' and 'end_date'
where the start_date and end_date changes every
month

dnoeth 4628 posts Joined 11/04
26 Aug 2014

I don't understand what you mean.
Using CURRENT_DATE automatically changes the result every month.

Dieter

PatelSatish 4 posts Joined 02/12
28 Aug 2014

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

You must sign in to leave a comment.