Master Calendar QVD

[priyarane]

Hi Experts,

If any one have master calendar QVD, then please share it with me.

Thank you

[NickHoff · Accepted solution]

//Calendar:

LET vDateMin = Num(MakeDate(2010,1,1)); 
//LET vDateMax = Floor(MonthEnd(Today())); 
LET vDateMax = Num(MakeDate(2020,6,30)); 
LET vDateToday = Num(Today()); 
LET PD = Date(makedate(2010,1,15));
LET vFactor = 6;   //offset for calculating fiscal year

TempCalendar: 
//Left Keep (PayCore)
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber, 
Date($(vDateMin) + RowNo() - 1) AS TempDate 
AUTOGENERATE 1 
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

SCM_CALENDAR_MASTER: 
LOAD
Date(TempDate) AS CalendarDate, 
Date(TempDate) AS  %DATE_KEY,

// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth, 
WeekDay(TempDate) AS CalendarDayName, 
Week(TempDate) AS CalendarWeekOfYear, 
Month(TempDate) AS CalendarMonthName, 
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter, 
Year(TempDate) AS CalendarYear, 

// Calendar Date Names 
WeekName(TempDate) as CalendarWeekNumberAndYear, 
MonthName(TempDate) as CalendarMonthAndYear, 
QuarterName(TempDate) as CalendarQuarterMonthsAndYear, 

// Start Dates 
DayStart(TempDate) as CalendarDayStart, 
WeekStart(TempDate) as CalendarWeekStart, 
MonthStart(TempDate) as CalendarMonthStart, 
QuarterStart(TempDate) as CalendarQuarterStart, 
YearStart(TempDate) as CalendarYearStart, 

// End Dates 
DayEnd(TempDate) as CalendarDayEnd, 
WeekEnd(TempDate) as CalendarWeekEnd, 
MonthEnd(TempDate) as CalendarMonthEnd, 
QuarterEnd(TempDate) as CalendarQuarterEnd, 
YearEnd(TempDate) as CalendarYearEnd, 

// Combo Date Examples 
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear, 
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter, 
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays, 

//Fiscal Dates
Num(Month(AddMonths(TempDate,$(vFactor))),00) AS FiscalMonthNum,
'FY ' & Right(Year(AddMonths(TempDate,$(vFactor))),2) AS FiscalYearDesc,
Year(AddMonths(TempDate,$(vFactor)))&'|'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as %Fiscal_Key,
Year(AddMonths(TempDate,$(vFactor)))&'-'&Num(Month(AddMonths(TempDate,$(vFactor))),00) as FYRPR,
Year(AddMonths(TempDate,$(vFactor))) as FiscalYear,
'FY ' & Right(Year(AddMonths(TempDate,$(vFactor))),2)& ' Q' & Ceil(Month(AddMonths(TempDate,$(vFactor)))/3) AS FiscalYearQuarter

RESIDENT TempCalendar ORDER BY TempDate ASC; 

DROP TABLE TempCalendar; 

$(Include=..\includes\qvdstoredrop.txt);