HI @its_rajvir
Basically a subroutine allows you to write script and then run it when required instead of the just running down from top to bottom. The most common use of subroutine is i have used are
- If statement - if you only need to run script under certain criteria
- Reusing script - Loops or multiple joins.
I am sure their are lot of other reasons.
I have used it in my Calendar script i reuse, to allow me to easier create a standard calendar, fiscal calendar or both.
/*************************************[Calendar]**************************************/
//Variables for the table and date fields to link to your Data.
SET vDateField ='Date'; //Change value to the field name of your date you are linking on
SET vTableName = 'DATA'; //Change to the table name where your date field is located
//Variables to turn on routines
SET vFinancial = 1; //Replace for 1 or 0 to turn on or off the financial Calender
SET vWeekOS = 0; //Replace for 1 or 0 to turn on or off the financial week start off set
// Checks the above variables are set to 1 if not resets to 0.
LET vFinancial = IF($(vFinancial) <> 1, 0, $(vFinancial));
LET vWeekOS = IF($(vWeekOS) <> 1, 0, $(vWeekOS));
//These variables are used for your date off sets i.e. Financial Year
SET vFinYearOS = -3; //Replace this for the Month number of your financial start date i.e. 3 = April, As offset keep as a minus figure
SET vFinWeekOS = 1; //Replace this with off set for you finanical week start i.e. -1 will start the week on Sunday
Range:
LOAD
min($(vDateField)) as startdate, //Replace "Your Date" with the date field name, get the minimum date in the table
max($(vDateField)) as enddate //Replace "Your Date" with the date field name, get the maximum date in the table
resident $(vTableName); //Repoint at your table
//Peek out the values in to variables for later use
let vStart = peek('startdate',-1,'Range')-1;
let vEnd = peek('enddate',-1,'Range');
let vRange = $(vEnd) - $(vStart);
TRACE $(vStart);
TRACE $(vEnd);
TRACE $(vRange);
//Remove Range table as no longer needed
Drop table Range;
//Generate a table with a row per date between the range above
TempCalendar:
Load
$(vStart)+recno() as TempDate
autogenerate $(vRange);
//Creates the Sub Routine to create a the standard calendar
SUB Calendar
MasterCalendar:
LOAD *,
IF($(vWeekOS) =1, //If there Week start off set
DIV($(vDateField)-CAL_WEEK_START,7)+1,
WEEK($(vDateField))) AS CAL_WEEK;
LOAD *,
DATE(YEARSTART($(vDateField))-WEEKDAY(YEARSTART($(vDateField)))-$(vFinWeekOS)) AS CAL_WEEK_START;
LOAD
DATE(TempDate) AS $(vDateField),
NUM(TempDate) AS $(vDateField)_NUM,
/**Script to make needed dates based on the Calendar Year**/
YEAR(TempDate) AS CAL_YEAR,
CAPITALIZE(REPLACE(MONTH(TempDate),'.',''))&'-'&YEAR((TempDate)) AS CAL_MONTH_YEAR,
MONTH(TempDate) AS CAL_MONTH,
NUM(MONTH(TempDate)) AS CAL_MONTH_NO,
WEEKDAY(TempDate) AS CAL_WEEKDAY,
DAY(TempDate) AS CAL_DAY,
DUAL('Q' & CEIL(MONTH(TempDate)/3), CEIL(MONTH(TempDate)/3)) AS CAL_QUARTER,
'Q' & CEIL(MONTH(TempDate)/3) AS CAL_QUARTER_DESC,
CEIL(MONTH(TempDate) /3) AS CAL_QUARTER_NO,
/**Script to make flags for use in set analysis**/
IF(YEAR(TempDate)=YEAR(TODAY(1)),1) AS F_CAL_CURRENT_YEAR,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(TODAY(1)), 1) AS F_CAL_CURRENT_QUARTER,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(TODAY(1)) AND DATE(TempDate) >= TODAY(1),1) AS F_CAL_QTD,
IF(MONTH(TempDate)= MONTH(TODAY(1)) AND YEAR(TempDate)=YEAR(TODAY(1)),1) AS F_CURRENT_MONTH,
IF(WEEK(TempDate)= WEEK(TODAY(1)) AND YEAR(TempDate)=YEAR(TODAY(1)),1) AS F_CURRENT_WEEK,
IF(TempDate = TODAY(1)-1, 1) AS F_YESTERDAY_FLAG,
IF(YEAR(TempDate)=YEAR(TODAY(1))-1,1) AS F_CAL_PRIOR_YEAR,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),-3))/3) AND YEAR(TempDate) = YEAR(TODAY(1)), 1) AS F_PRIOR_QUARTER,
IF(MONTH(TempDate)= MONTH(ADDMONTHS(TODAY(1),-1)) AND YEAR(TempDate)=YEAR(ADDYEARS(TODAY(1),-1)),1) AS F_PRIOR_MONTH,
IF(WEEK(TempDate)= WEEK(TODAY(1)-7) AND YEAR(TempDate)=YEAR(ADDYEARS(TODAY(1),-1)),1) AS F_PRIOR_WEEK,
IF(YEAR(TempDate)= YEAR(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_CAL_YTD,
IF(YEAR(TempDate)= YEAR(TODAY(1)) AND MONTH(TempDate)= MONTH(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_MTD,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(ADDYEARS(TODAY(1),-1)), 1) AS F_CAL_QUATER_LY,
IF(CEIL(MONTH(TempDate)/3) = CEIL(MONTH(TODAY(1))/3) AND YEAR(TempDate) = YEAR(ADDYEARS(TODAY(1),-1)) AND DATE(TempDate) >= ADDYEARS(TODAY(1),-1),1) AS F_CAL_QTD_LY,
IF(MONTH(TempDate) = MONTH(ADDYEARS(TODAY(1),-1)),1) AS F_MONTH_LY,
IF(TempDate = ADDYEARS(TODAY(1),-1),1) AS F_DAY_LY,
IF(YEAR(TempDate)= YEAR(ADDYEARS(TODAY(1),-1)) AND TempDate <= ADDYEARS(TODAY(1),-1),1) AS F_CAL_LYD,
IF(YEAR(TempDate)= YEAR(ADDYEARS(TODAY(1),-1)) AND MONTH(TempDate)= MONTH(ADDYEARS(TODAY(1),-1)) AND TempDate <= ADDYEARS(TODAY(1),-1),1) AS F_LYMD,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-7),1) AS F_ROLLING_7,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-30),1) AS F_ROLLING_30,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-60),1) AS F_ROLLING_60,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-90),1) AS F_ROLLING_90,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-180),1) AS F_ROLLING_180,
IF(TempDate <= TODAY(1) AND TempDate >= (TODAY(1)-365),1) AS F_ROLLING_365,
IF(TempDate <= MONTHSTART(TODAY(1)) AND TempDate >= ADDMONTHS(MONTHSTART(TODAY(1)),-12),1) AS F_ROLLING_12_MONTHS
Resident TempCalendar
Order By TempDate ASC;
ENDSUB
//Creates the Sub Routine to create a the Financial calendar
SUB Financial
FinancialCalendar:
LOAD *,
IF($(vWeekOS) =1, //If there Week start off set
DIV($(vDateField)-FIN_WEEK_START,7)+1,
WEEK(ADDMONTHS($(vDateField),$(vFinYearOS)))) AS FIN_WEEK;
LOAD *,
APPLYMAP('FinWeekMap',FIN_YEAR_START) AS FIN_WEEK_START;
LOAD
DATE(TempDate) AS $(vDateField),
NUM(TempDate) AS $(vDateField)_NUM,
/**Script to make needed dates based on the Fiscal Year**/
YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))&'/'&YEAR(ADDYEARS(ADDMONTHS(TempDate,$(vFinYearOS)),1)) AS FIN_YEAR,
YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) AS FIN_YEAR_START,
NUM(MONTH(ADDMONTHS(TempDate,$(vFinYearOS))))&'-'&YEAR((ADDMONTHS(TempDate,$(vFinYearOS)))) AS FIN_MONTH_YEAR,
NUM(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))) AS FIN_MONTH_NO,
DAY(ADDMONTHS(TempDate,$(vFinYearOS))) AS FIN_DAY,
DUAL('Q' & CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3), CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3)) AS FIN_QUARTER,
'Q' & CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) AS FIN_QUARTER_DESC,
CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) AS FIN_QUARTER_NO,
/**Script to make flags for use in set analysis**/
IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))= YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))),1) AS F_FIN_CURRENT_YEAR,
IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))=YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS)))-1,1) AS F_FIN_PRIOR_YEAR,
IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))= YEAR(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_FIN_YTD,
IF(YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))= YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))) AND TempDate <= ADDYEARS(TODAY(1),-1),1) AS F_FIN_LYD,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))), 1) AS F_FIN_CURRENT_QUARTER,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS)))
AND DATE(ADDMONTHS(TempDate,$(vFinYearOS))) >= ADDMONTHS(TODAY(1),$(vFinYearOS)),1) AS F_FIN_QTD,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-3))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDMONTHS(TODAY(1),$(vFinYearOS))), 1) AS F_FIN_PRIOR_QUARTER,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDYEARS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-1)), 1) AS F_FIN_QUATER_LY,
IF(CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) = CEIL(MONTH(ADDMONTHS(TODAY(1),$(vFinYearOS)))/3)
AND YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) = YEAR(ADDYEARS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-1))
AND DATE(ADDMONTHS(TempDate,$(vFinYearOS))) >= ADDYEARS(ADDMONTHS(TODAY(1),$(vFinYearOS)),-1),1) AS F_FIN_QTD_LY
Resident TempCalendar
Order By TempDate ASC;
ENDSUB
IF $(vFinancial) = 0 THEN //If financial calendar is is off then just build a normal calendar
CALL Calendar;
ELSE //Created both normal calendar and financial
CALL Calendar;
CALL Financial;
NOCONCATENATE
Calendar:
LOAD
*
RESIDENT MasterCalendar;
LEFT JOIN (Calendar)
LOAD
*
RESIDENT FinancialCalendar;
DROP TABLES MasterCalendar, FinancialCalendar;
ENDIF;
DROP TABLE TempCalendar;
EXIT SCRIPT;
