gistfile1.c

QuartersMap:  
MAPPING LOAD   
rowno() as Month,  
'Q' & Ceil (rowno()/3) as Quarter  
AUTOGENERATE (12);  
  
//Create min and max date (max=current date for the assignment)
Temp:
load date(minDate,'YYYY-MM-DD') AS minDate,  date(maxDate,'YYYY-MM-DD') AS maxDate inline [
minDate, maxDate
'2005-12-30', '2009-06-30'
];

SET vFiscalYearStartMonth = 7;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));  
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));  
DROP Table Temp;  
  
TempCalendar:  
LOAD  
               $(varMinDate) + Iterno()-1 As Num,  
               Date($(varMinDate) + IterNo() - 1) as TempDate  
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);  
  
MasterCalendar:  
LOAD
*,
	Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
	Dual(Text(Date(MonthEnd(Key_OrderDate), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name
Load  
//Master Calendar will Join its fields with Key_OrderDate field From our DB
               TempDate AS Key_OrderDate,  
               week(TempDate) As OrderWeek,  
               Year(TempDate) As OrderYear,  
               Month(TempDate) As OrderMonth,  
               Day(TempDate) As OrderDay,  
               YeartoDate(TempDate)*-1 as OrderCurYTDFlag,  
               YeartoDate(TempDate,-1)*-1 as OrderLastYTDFlag,  
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as OrderRC12,  
               date(monthstart(TempDate), 'MMM-YYYY') as OrderMonthYear,  
               ApplyMap('QuartersMap', month(TempDate), Null()) as OrderQuarter,  
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as OrderWeekYear,  
               WeekDay(TempDate) as OrderWeekDa,
               
		    Mod(Month(TempDate) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month
	         YearName(TempDate, 0, $(vFiscalYearStartMonth)) AS FiscalYear  // Fiscal Calendar Year
Resident TempCalendar  
Order By TempDate ASC;  
Drop Table TempCalendar;