GetCalendarDate function
Syntax
GetCalendarDate(comparedate, periods, periodadjustment,outfieldname, company, paygroup)Description
Use the GetCalendarDate function to return the value of a Date field from the PS_PAY_CALENDAR table. If a table entry is not found, GetCalendarDate returns 1899-01-01.
Parameters
| Parameter | Description |
|---|---|
|
comparedate |
A date field set by the caller as the date of interest, for example, "1997-02-17." |
|
periods |
A numeric variable set by the caller specifying the number of periods forward or backward to be returned. |
|
periodadjustment |
A numeric variable that adjusts the periods if the comparedate equals the period end date. This is typically used to adjust for period end dates. Usually the periodadjustment is either -1, 0, or 1. |
|
outfieldname |
The name of a date field in the PS_PAY_CALENDAR table. For example PAY_BEGIN_DT. The value of this field is not referenced or modified by the routine, but the name of the field is used to build a SQL SELECT statement and to indicate which value from the table to return in the return date. |
|
company |
A field set by the caller to be equal to the company code of interest, for example, "CCB". |
|
paygroup |
A variable set by the caller to be equal to the PayGroup code of interest, for example, "M01". |
Returns
Returns a Date value from the PS_PAY_CALENDAR table.
Example
The following examples use the sample PS_PAY_CALENDAR entries in the following table. In the example, comparedate and the result date are Date type fields defined in some record.
| COMPANY | PAYGROUP | PAY_END_DT | PAY_BEGIN_DT | CHECK_DT |
|---|---|---|---|---|
|
CCB |
MO1 |
1997-01-31 |
1997-01-01 |
1997-01-31 |
|
CCB |
MO1 |
1997-02-28 |
1997-02-01 |
1997-02-28 |
|
CCB |
MO1 |
1997-03-31 |
1997-03-01 |
1997-03-29 |
|
CCB |
MO1 |
1997-04-30 |
1997-04-01 |
1997-04-30 |
|
CCB |
MO1 |
1997-05-31 |
1997-05-01 |
1997-05-31 |
|
CCB |
MO1 |
1997-06-30 |
1997-06-01 |
1997-06-28 |
|
CCB |
MO1 |
1997-07-31 |
1997-07-01 |
1997-07-31 |
|
CCB |
MO1 |
1997-08-31 |
1997-08-01 |
1997-08-30 |
|
CCB |
MO1 |
1997-09-30 |
1997-09-01 |
1997-09-30 |
|
CCB |
MO1 |
1997-10-31 |
1997-10-01 |
1997-10-31 |
|
CCB |
MO1 |
1997-11-30 |
1997-11-01 |
1997-11-27 |
|
CCB |
MO1 |
1997-12-31 |
1997-12-01 |
1997-12-31 |
|
CCB |
SM1 |
1997-01-15 |
1997-01-01 |
1997-01-15 |
Find the begin date of the pay period containing the date 1997-05-11 (the value of &COMPAREDate). The result date returned would be 1997-05-01.
&RESULT_Date = GetCalendarDate(&COMPAREDate, 0, 0,
PAY_BEGIN_DT, COMPAny, PAYGROUP);Or:
&RESULT_Date = GetCalendarDate(&COMPAREDate, 1, -1,
PAY_BEGIN_DT, COMPAny, PAYGROUP);Processing Rules
The following are the processing rules for GetCalendarDate:
-
The function SELECTs all the values for outfieldname, PAY_BEGIN_DT and PAY_END_DT from PS_PAY_CALENDAR. The result set is sorted in increasing PAY_END_DT order.
-
A SQL SELECT statement is generated in the following form:
-
SELECT outfieldname, PAY_BEGIN_DT, PAY_END_DT FROM PS_PAY_CALENDAR WHERE COMPAny=:1 AND PAYGROUP=:2 ORDER BY PAY_END_DT;
-
Rows are fetched from the result set until the value of comparedate falls between PAY_BEGIN_DT and PAY_END_DT. The value of outfieldname is stored in a storage stack.
-
A work variable equal to the value in periods is set.
-
If the value of outfieldname in the located result row is equal to comparedate, then the value in periodadjustment is added to the work variable. Because periodadjustment may be negative, the result may be negative.
-
If the work variable is negative then the saved value of outfieldname is returned from the storage stack at the level specified by the work variable. If the work variable is positive then fetch forward the number of times specified by the work variable. The value of outfieldname is returned from the most recently fetched (current) row.