PeopleSoft Expression How to #2

THEN B.TJ_TOTAL_BASE_COMP * (B.TJ_EP_PCT_ENTRY/100) * (N.TJ_EP_PILL_WEIGHT/100) * (G.EP_WEIGHT/100) * (B.TJ_EP_PCT_SALARY/100) *(B.TJ_EP_PRORATE_PC/100)

WHEN E.EP_SECTION_TYPE ='TJEXG' AND G.REVIEW_RATING = '2'

THEN B.TJ_TOTAL_BASE_COMP * (B.TJ_EP_PCT_TARGET/100) * (N.TJ_EP_PILL_WEIGHT/100) * G.EP_WEIGHT/100) * (B.TJ_EP_PCT_SALARY/100) *(B.TJ_EP_PRORATE_PC/100)

WHEN E.EP_SECTION_TYPE ='TJEXG' AND G.REVIEW_RATING = '3'

THEN B.TJ_TOTAL_BASE_COMP * (B.TJ_EP_PCT_MAX/100) * (N.TJ_EP_PILL_WEIGHT/100) * (G.EP_WEIGHT/100) * (B.TJ_EP_PCT_SALARY/100)

WHEN E.EP_SECTION_TYPE <>'TJEXG' AND G.REVIEW_RATING = '1'

THEN B.TJ_TOTAL_BASE_COMP * (B.TJ_EP_PCT_ENTRY/100) * (N.TJ_EP_PILL_WEIGHT/100) * (N.TJ_EP_WEIGHT2/100) * (B.TJ_EP_PCT_SALARY/100)

WHEN E.EP_SECTION_TYPE <>'TJEXG' AND G.REVIEW_RATING = '2'

THEN B.TJ_TOTAL_BASE_COMP * (B.TJ_EP_PCT_TARGET/100) * (N.TJ_EP_PILL_WEIGHT/100) * (N.TJ_EP_WEIGHT2/100) * (B.TJ_EP_PCT_SALARY/100)

ELSE 0

END

Calculate Monthly Rate using Per Period Rate and Pay Frequency

CASE

WHEN H.PAY_FREQUENCY = 'B' THEN (D.DEDN_AMT_B_TAX *26/12)

WHEN H.PAY_FREQUENCY = 'S' THEN (D.DEDN_AMT_B_TAX *24/12)

END

Calculate Monthly Rate using Per Period Rate, Pay Group and Paycheck Current Deduction

In this example, the amounts returned are summed.

SG Action Template - Return ‘Y’ when an employee is older than 39.999 years

CASE

WHEN MONTHS_BETWEEN(SYSDATE,G.BIRTHDATE)/12

between 39.999 and 120

THEN 'Y'

END

SG PTO Report - Return ‘PTO Cancelled’ when hours equal 0

CASE
WHEN A.TL_QUANTITY < 0
THEN 'PTO Cancelled'
END

SG PTO Report - Return ‘Planned PTO’ when DUR > SYSDATE

CASE
WHEN A.DUR > SYSDATE
THEN 'Planned PTO'
END

SG Workers’ Comp - Designates Executive/Non-Executive for the Workers Comp Report for a specified population:

CASE
WHEN A.EMPLID in ('01000320495',
'GL001351584',
'01000301379',
'GL001362177',
'GL001277342',
'01000308375',
'01000319162',
'01000006170',
'01000306538',
'01000301745',
'01000311519',
'01000305083')
THEN 'Executive'
ELSE 'Non-Executive'
END

SG Workers’ Comp - Translates Company Codes

CASE WHEN A.COMPANY = 'SG' THEN 'Societe Generale'

WHEN A.COMPANY = 'SGA' THEN 'SG Americas Securities, LLC'

WHEN A.COMPANY = 'SGO' THEN 'SG Americas Operational Services, Inc'

WHEN A.COMPANY = 'SGI' THEN 'SG Investment Corp.'

WHEN A.COMPANY = 'AEQ' THEN 'SG Americas Securities'

WHEN A.COMPANY = 'CAM' THEN 'Lyxor Asset Management' WHEN A.COMPANY = 'ENR' THEN 'SG Energy' ELSE '' END

SG Workers’ Comp – Add 3% to Annual Salary for projected total comp

(A.ANNUAL_RT*.03)+A.ANNUAL_RT+D.SG_LAST_BONUS_AMT+D.SG_LAST_GUAR_AMT

Return Age Range based on employee Birth Date (see third expression)

Return lesser of 16667 or annual rate and Preform calculation on result

SG - Expression which creates a blank column

Enter ‘’ (2 single quotes) and change the query Heading Text to the desired column heading

SG PTO Report - Return Quarter based on date range

CASE

WHEN A.DUR BETWEEN '2019-01-01' and '2019-03-31'

THEN 'Quarter 1'

WHEN A.DUR BETWEEN '2019-04-01' and '2019-06-30'

THEN 'Quarter 2'

WHEN A.DUR BETWEEN '2019-07-01' and '2019-09-30'

THEN 'Quarter 3'

WHEN A.DUR BETWEEN '2019-10-01' and '2019-12-31'

THEN 'Quarter 4'

END

SG PTO Report - Return Month based on date range – 2019, 2020

CASE

WHEN A.DUR BETWEEN '2019-01-01' and '2019-01-31'

THEN 'January'

WHEN A.DUR BETWEEN '2019-02-01' and '2019-02-29'

THEN 'February'

WHEN A.DUR BETWEEN '2019-03-01' and '2019-03-31'

THEN 'March'

WHEN A.DUR BETWEEN '2019-04-01' and '2019-04-30'

THEN 'April'

WHEN A.DUR BETWEEN '2019-05-01' and '2019-05-31'

THEN 'May'

WHEN A.DUR BETWEEN '2019-06-01' and '2019-06-30'

THEN 'June'

WHEN A.DUR BETWEEN '2019-07-01' and '2019-07-31'

THEN 'July'

WHEN A.DUR BETWEEN '2019-08-01' and '2019-08-31'

THEN 'August'

WHEN A.DUR BETWEEN '2019-09-01' and '2019-09-30'

THEN 'September'

WHEN A.DUR BETWEEN '2019-10-01' and '2019-10-31'

THEN 'October'

WHEN A.DUR BETWEEN '2019-11-01' and '2019-11-30'

THEN 'November'

WHEN A.DUR BETWEEN '2019-12-01' and '2019-12-31'

THEN 'December'

WHEN A.DUR BETWEEN '2020-01-01' and '2020-01-31'

THEN 'January'

WHEN A.DUR BETWEEN '2020-02-01' and '2020-02-29'

THEN 'February'

WHEN A.DUR BETWEEN '2020-03-01' and '2020-03-31'

THEN 'March'

WHEN A.DUR BETWEEN '2020-04-01' and '2020-04-30'

THEN 'April'

WHEN A.DUR BETWEEN '2020-05-01' and '2020-05-31'

THEN 'May'

WHEN A.DUR BETWEEN '2020-06-01' and '2020-06-30'

THEN 'June'

WHEN A.DUR BETWEEN '2020-07-01' and '2020-07-31'

THEN 'July'

WHEN A.DUR BETWEEN '2020-08-01' and '2020-08-31'

THEN 'August'

WHEN A.DUR BETWEEN '2020-09-01' and '2020-09-30'

THEN 'September'

WHEN A.DUR BETWEEN '2020-10-01' and '2020-10-31'

THEN 'October'

WHEN A.DUR BETWEEN '2020-11-01' and '2020-11-30'

THEN 'November'

WHEN A.DUR BETWEEN '2020-12-01' and '2020-12-31'

THEN 'December'

END

SG Case Translation for SG Custom Empl Class to Internal/External/Other

CASE
WHEN A.SG_EMP_CLASS_DESCR = 'Consultant'
THEN 'External'
WHEN A.SG_EMP_CLASS_DESCR = 'Contractor'
THEN 'External'
WHEN A.SG_EMP_CLASS_DESCR = 'Expat (Detache)'
THEN 'Internal'
WHEN A.SG_EMP_CLASS_DESCR = 'Intern'
THEN 'Internal'
WHEN A.SG_EMP_CLASS_DESCR = 'Intern>1000 Hrs'
THEN 'Internal'
WHEN A.SG_EMP_CLASS_DESCR = 'Local Employee'
THEN 'Internal'
WHEN A.SG_EMP_CLASS_DESCR = 'Stagiaire'
THEN 'Other'
WHEN A.SG_EMP_CLASS_DESCR = 'Third Country National'
THEN 'Other'
WHEN A.SG_EMP_CLASS_DESCR = 'Third Country National'
THEN 'Other'
END

SG PTO Report - Return Year based on date range

CASE

WHEN A.DUR BETWEEN '2019-01-01' and '2019-12-31'

THEN '2019'

WHEN A.DUR BETWEEN '2020-01-01' and '2020-12-29'

THEN '2020'

END

Return text based on a data condition

CASE

WHEN A.TL_QUANTITY < 0

THEN 'PTO Cancelled'

END

SG Holiday Schedule and Work Group Audit

CASE

WHEN B.FLSA_STATUS = 'E'

AND A.WORKGROUP NOT IN ('EX MONTHUR','EX SG','EX SGA','EXPAT','EXPAT SGA','MSCNOOT','MSCNOOTQEC','CANMONNOOT','EX CA SG','EX CA SGA','MSC_SGB','CALGARY')

THEN 'Exempt employee in Non-Exempt Work Group'

WHEN B.FLSA_STATUS = 'N'

AND A.WORKGROUP NOT IN ('CANMON OT','CANTOR OT','CANTORNOOT','DET N SGA','EXPAT N SG','MSCOTQEC','MSCOTSGB','MSCOTSGC','NEX CA SG','NEX CA SGA','NONEXEMPT','SGAS NONEX','USINTERN')

THEN 'Non-Exempt employee in Exempt Work Group'

WHEN B.FLSA_STATUS = 'X'

AND A.WORKGROUP NOT IN ('VIE','VIE CANMON','VIE CANTOR', 'VIE SG')

THEN 'VIE in wrong Work Group'

END

CASE
WHEN B.COMPANY IN ('SG','SGO','EQP')
AND B.HOLIDAY_SCHEDULE <> 'SGB'
THEN 'Holiday Schedule should be SGB'

WHEN B.COMPANY IN ('SGA','CAM')
AND B.HOLIDAY_SCHEDULE <> 'SGC'
THEN 'Holiday Schedule should be SGC'
END

DECODE

Works like an IF or CASE WHEN statement. This is good for doing many IF statement instead of doing many CASE WHEN statements.

Expression: decode(expression, search, result, default)

table.fieldname
WHEN 'PS value1' THEN 'your value'
WHEN 'PS value2' THEN 'your value'
WHEN 'PS value3' THEN 'your value'
ELSE 'your value'
END

SUBSTRING

Returns NID with Dashes

Adds the dashes in the NID “123-45-6789”

Expression: SUBSTR(B.NATIONAL_ID,1,3) || '-' || SUBSTR(B.NATIONAL_ID,4,2) || '-' || SUBSTR(B.NATIONAL_ID,6,4)

CONCATENATE

Expression: A.FIRST_NAME || A.LAST_NAME

Expression: CONCAT(A.FIRST_NAME,A.LAST_NAME)

Expression: A.FIRST_NAME || ' ' || A.LAST_NAME

Expression: A.LAST_NAME || ', ' || A.FIRST_NAME

Use a list of values as query criteria

Use Condition Type ‘equal to’ and the expression below

Expression: any ('123456','234567')

SUBSTRING

Returns part of a field value.

SUBSTR (B.POSTAL,1,5) – to eliminate the zip code extension

Expression: SUBSTR(A.DEPTID,1,4)

1 = the position to start returning data

4 = number of positions to return

Outer Join with Additional Criteria

For outer joins with additional criteria

SG Action Template - Outer Joins to return 3 data sets (this example: 2016, 2017, 2018) from the same table:

SG Action Template - Outer Joins to return current and historical Annual Salary

F (JOB) is joined to A to return employees’ current salary and

H (JOB) is has a left outer join to A to return employees’ salary as of 4/1/2020:

Expression to Return Leave Accrual Balance

Divide a field value by another field value

A.TL_QUANTITY/D.SCHED_HRS

Running queries which attempt to return too much data

Schedule Query

Select the XLS Format

More Date Expressions

Calculate the difference between two date-time fields

Expression: This will display number of days. If less then 24 hours it shows as a fraction of an hour:

TO_DATE(SUBSTR(A.HRS_ROW_UPD_DTTM,1,16),'YYYY-MM-DD-HH24.MI') - TO_DATE(SUBSTR(A.HRS_ROW_ADD_DTTM,1,16),'YYYY-MM-DD-HH24.MI')
To convert it to hours, multiply by 24: (TO_DATE(SUBSTR(A.HRS_ROW_UPD_DTTM,1,16),'YYYY-MM-DD-HH24.MI') - TO_DATE(SUBSTR(A.HRS_ROW_ADD_DTTM,1,16),'YYYY-MM-DD-HH24.MI')) * 24
Format: Why it is useful: Date time stamps are used for auditing and show when data is initially entered and then modified. For Recruiting, we've used this to show how long it took to have an applicant go from Prepared for hire status to actually hired in the system

Date Expression to return 1/1/XXXX Beginning of year for current year.

Expression: %datein(%SQL(FUNCLIB_HR_DATE_YEAR,%currentdatein) %concat '01-01')

Expression 2: TO_DATE('01-01-'||TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY')

Output Next Month (based on current month), e.g. 03/2015:

Expression: TO_CHAR(ADD_MONTHS(SYSDATE, +1),'MM/YYYY')

Output Previous Month (based on current month), e.g. 01/2015:

Expression: TO_CHAR(ADD_MONTHS(SYSDATE, -1),'MM/YYYY')

Output Last Day of Previous Month (based on current month), e.g. 31-JAN-2015:

Expression: TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)),'DD-MON-YYYY')

Output Last Day of Current Month (based on current month), e.g. 28-FEB-2015:

Expression: TO_CHAR(LAST_DAY(SYSDATE),'DD-MON-YYYY')

First and Last day of month

Last day of the previous month: LAST_DAY(SYSDATE - 1)

First day of the previous month: LAST_DAT(ADD_MONTHS(SYSDATE, - 2)) + 1)

Query Join JOB to Base Benefits Table when Multiple Jobs functionality is implemented

A.JOB to B.PER_ORG_ASGN

EMPLID

EMPL_RCD

A.JOB to C.PRIMARY_JOBS

EMPLID

EMPL_RCD

C.PRIMARY_JOBS.PRIMARY_JOB_IND = ‘Y’

B.PER_ORG_ASSN to Base Benefits Table

EMPLID

PER_ORG_ASSN.BENEFIT_RCD_NBR = base ben table.EMPL_RCD

You always need to add PRIMARY_JOBS when you join JOB and any Benefits table:

SELECT A.EMPLID, A.EMPL_RCD, A.COMPANY, B.BENEFIT_RCD_NBR, C.PRIMARY_JOB_IND, D.EMPLID, D.EMPL_RCD, D.COMPANY, D.PLAN_TYPE, TO_CHAR(D.ACCRUAL_PROC_DT,'YYYY-MM-DD')
FROM PS_JOB A,

PS_PER_ORG_ASGN B,

PS_PRIMARY_JOBS C,

              PS_LEAVE_ACCRUAL D
WHERE A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPL_RCD = B.EMPL_RCD
     AND A.EMPLID = C.EMPLID
     AND A.EMPL_RCD = C.EMPL_RCD
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_PRIMARY_JOBS C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.PRIMARY_JOB_APP = C_ED.PRIMARY_JOB_APP
          AND C.EMPL_RCD = C_ED.EMPL_RCD
          AND C_ED.EFFDT <= SYSDATE)
     AND C.PRIMARY_JOB_IND = 'Y'
     AND B.EMPLID = D.EMPLID
     AND B.BENEFIT_RCD_NBR = D.EMPL_RCD

Joins:

1. JOB to PER_ORG_ASGN

· EMPLID

· EMPL_RCD

2. JOB to PRIMARY_JOBS

· EMPLID

· EMPL_RCD

3. PRIMARY_JOBS.PRIMARY_JOB_IND = ‘Y’

4. PER_ORG_ASGN to ben table

· EMPLID

· PER_ORG_ASGN.BENEFIT_REC_NBR = base benefits table.EMPL_RCD

Built-in Functions

PeopleSoft delivers built-in functions that you can use to make expressions powerful and flexible. You can also nest built-in functions and expressions; for example:

· Concatenate two fields, separate them with a comma, drop trailing blanks, and limit this field to 40 characters:

Substring(RTRIM(FirstName) | , | RTRIM(LastName),1,40)

· Sum the multiplication of two fields:

SUM(PROD_COST * PROD_RATE)

This table lists all of the delivered built-in functions with supported meta-SQL and aggregate functions:

Function	Explanation	Use
ABS(Number)	Returns the absolute value of the specified number.	ABS(-5) returns 5.
AddMonth(Date, Number)	Adds a number of months to the specified date and returns the calculated date.	AddMonth(“2002–01–10”,2) returns 2002–03–10.
Average(Number)	Returns the average value of the specified number (typically a numeric column name).	Average(PRICE) returns the average price of your source data set.
Beginning of Month(Date)	Returns a date representing the first day of the month for the specified date.	Beginning of Month(“2002–10–05”) returns 2002–10–01.
Beginning of Year(Date)	Returns a date representing the first day of the year for the specified date.	Beginning of Month(“2002–10–05”) returns 2002–01–01.
Count(col)	Returns the number of rows that are found.	Count(PRODUCT_ID) returns the number of rows on your source data set.
DateAdd(Date, Days)	Adds a number of days to the specified date and returns the calculated date.	DateAdd(“2002–02–02”,5) returns 2002–02–07.
DateDiff(Date, Date)	Returns the number of days that have elapsed between the first and second specified dates.	DateDiff(“2002–02–02”,”2002–02–10”) returns 8.
DtTmDiff(Datetime,Datetime)	Returns the number of minutes that have elapsed between the first and second specified datetimes.	DtTmDiff(”'2002-01-01 00:00:00.000”,”2002-01-01 10:00:00.000”) returns 600 (10 hours in minutes).
DIFF_H(Datetime, Datetime)	Returns the number of hours that have elapsed between the first and second specified datetimes.	DIFF_H(”'2002-01-01 00:00:00.000”,”2002-01-01 10:00:00.000”) returns 10.
DIFF_S (Datetime, Datetime)	Returns the number of seconds that have elapsed between the first and second specified datetimes.	DIFF_S(”'2002-01-01 00:00:00.000”,”2002-01-01 10:00:00.000”) returns 36000 (10 hours in seconds).
End of Month(Date)	Returns a date representing the last day of the month for the specified date.	End of Month(“2002–10–05”) returns 2002–10–31.
End of Year(Date)	Returns a date representing the last day of the year for the specified date.	End of Year(“2002–10–05”) returns 2002–12–31.
GetDay(Date)	Returns the day of the month for the specified date.	GetDay(“2002–10–05”) returns 5.
GetMonth(Date)	Returns the month of the specified date.	GetMonth(“2002–10–05”) returns 10.
GetYear(Date)	Returns the year of the specified date.	GetYear(“2002–10–05”) returns 2002.
MOD(Number, Number)	Returns the modulus (or remainder) of the first number when it is divided by the second number.	MOD(14,3) returns 2 (14 divides into 3 four times with a remainder of 2).
Max(Number)	Returns the maximum value of the specified number (typically a numeric column name).	Max(PRICE) returns the largest price of your source data set.
Min (Number)	Returns the minimum value of the specified number (typically a numeric column name).	Min(PRICE) returns the smallest price of your source data set.
Sum(Number)	Returns the sum of the specified number (typically a numeric column name).	Sum(PRICE) returns the sum of all prices of your source data set.
RTRIM(String)	Returns the specified string, and trims trailing blank characters.	RTRIM(“Hello “) returns Hello.
Round(Number, Factor)	Returns a number representing the specified number after being rounded to the given factor or precision.	Round(5.582, 1) returns 5.6. Round(5.512, 0) returns 6.
Substring(String, StartPos, Length)	Returns a section of the specified string starting at the specified position and continuing for the specified length.	Substring(“My String Expression“, 4,6) returns String.
TO_CHAR(Number)	Returns a string representation of the specified numeric expression.	TO_CHAR(59) returns 59.

TO_NUM(String)	Returns a numeric representation of the specified string expression.	TO_NUM(“59”) returns 59. Note: Trying to apply a TO_NUM built-in function on a character that does not translate to a numeric value aborts the processing of your map.
TrimSubStr(String, Start, Length)	Returns a section of the specified string starting at the specified position and continuing for the specified length, and trims trailing blank characters.	Substring(“My String Expression“, 4,7) returns String.
Truncate (Number, Factor)	Returns a number representing the specified number after being truncated to the given factor or precision.	Truncate(5.582, 1) returns 5.5. Truncate(5.512, 0) returns 5.
UPPER(String)	Returns the specified string in uppercase characters.	UPPER(“My String Expression“, 4,7) returns MY STRING EXPRESSION.
YYMMDD(Date)	Returns the string representation of a specified date in YYMMDD format.	YYMMDD(“2002–10–01”) returns 021001.

Join JOB to JOB

Joins the max effective dates row to the max historical row. This example joins the historical row to the first sequence numbered current row:

AND D.EFFDT =
        (SELECT MAX(D_ED.EFFDT) FROM PS_JOB D_ED
        WHERE D.EMPLID = D_ED.EMPLID
          AND D.EMPL_RCD = D_ED.EMPL_RCD
          AND D_ED.EFFDT < A.EFFDT)
    AND D.EFFSEQ =
        (SELECT MIN(D_ES.EFFSEQ) FROM PS_JOB D_ES
        WHERE D.EMPLID = D_ES.EMPLID
          AND D.EMPL_RCD = D_ES.EMPL_RCD
          AND D.EFFDT = D_ES.EFFDT)

PeopleSoft Expression How to #2

Date Expressions

Calculate a person’s age

Calculate years based on a date field

SG 25 Years of Service - Calculate years of service as of the end of the year

SG Time Off for coming week - Return Data where the Effective Date is between 2 dynamic SYSDATE values

Suppress the birth year

Tenure in years based on Service Date

Return or use the last day of the current year

Months between a stored date and SYSDATE

Months between 2 dates:

Returns rows where the effective date is between the current date and the end of the week:

To select current data based on a ‘Year’ field

To strip the Year from a date field

SG RISQ Detail PTO - No MAP Data - Return Rows where the date is between 1/1 and 12/31 of the year the query is run

CASE WHEN

Case when will return a result when a field is in a condition

To determine STD coverage amount – and cap at 1000.00

Complex Performance Rating Case When expression with calculations

Calculate Monthly Rate using Per Period Rate and Pay Frequency

Calculate Monthly Rate using Per Period Rate, Pay Group and Paycheck Current Deduction

SG Action Template - Return ‘Y’ when an employee is older than 39.999 years

SG PTO Report - Return ‘PTO Cancelled’ when hours equal 0

SG PTO Report - Return ‘Planned PTO’ when DUR > SYSDATE

SG Workers’ Comp - Designates Executive/Non-Executive for the Workers Comp Report for a specified population:

SG Workers’ Comp - Translates Company Codes

SG Workers’ Comp – Add 3% to Annual Salary for projected total comp

Return Age Range based on employee Birth Date (see third expression)

Return lesser of 16667 or annual rate and Preform calculation on result

SG - Expression which creates a blank column

SG PTO Report - Return Quarter based on date range

SG PTO Report - Return Month based on date range – 2019, 2020

SG Case Translation for SG Custom Empl Class to Internal/External/Other

SG PTO Report - Return Year based on date range

Return text based on a data condition

SG Holiday Schedule and Work Group Audit

DECODE

SUBSTRING

Returns NID with Dashes

CONCATENATE

Use a list of values as query criteria

SUBSTRING

Outer Join with Additional Criteria

For outer joins with additional criteria

SG Action Template - Outer Joins to return 3 data sets (this example: 2016, 2017, 2018) from the same table:

SG Action Template - Outer Joins to return current and historical Annual Salary

Expression to Return Leave Accrual Balance

Divide a field value by another field value

Running queries which attempt to return too much data

Schedule Query

Select the XLS Format

More Date Expressions

Calculate the difference between two date-time fields

Date Expression to return 1/1/XXXX Beginning of year for current year.

Output Next Month (based on current month), e.g. 03/2015:

Output Previous Month (based on current month), e.g. 01/2015:

Output Last Day of Previous Month (based on current month), e.g. 31-JAN-2015:

Output Last Day of Current Month (based on current month), e.g. 28-FEB-2015:

First and Last day of month

Query Join JOB to Base Benefits Table when Multiple Jobs functionality is implemented

Built-in Functions

Excel Functions

Highlight alternating rows:

EXCEL – Calculates volume where returned amount ≤ 16667

Insert blank rows when key value changes