Steven Baumann

Creating elegant solutions to complex problems!

PeopleSoft Expression How to #2

 

 

 

PS Query Expressions


 

Table of Contents

Date Expressions. 3

Calculate a person’s age. 3

Calculate years based on a date field. 3

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

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

Suppress the birth year. 4

Tenure in years based on Service Date. 4

Return or use the last day of the current year. 4

Months between a stored date and SYSDATE. 5

Months between 2 dates: 5

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

To select current data based on a ‘Year’ field. 6

To strip the Year from a date field. 6

CASE WHEN.. 7

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

To determine STD coverage amount – and cap at 1000.00. 7

Complex Performance Rating Case When expression with calculations. 7

Calculate Monthly Rate using Per Period Rate and Pay Frequency. 8

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

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

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

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

SG Workers’ Comp - Translates Company Codes. 9

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

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

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

SG - Expression which creates a blank column. 11

SG PTO Report - Return Quarter based on date range. 12

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

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

SG PTO Report - Return Year based on date range. 14

Return text based on a data condition. 14

DECODE. 15

SUBSTRING.. 15

Returns NID with Dashes. 15

CONCATENATE. 15

Use a list of Employee IDs as query criteria. 16

SUBSTRING.. 17

Outer Join with Additional Criteria. 18

For outer joins with additional criteria. 18

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

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

Expression to Return Leave Accrual Balance. 19

Divide a field value by another field value. 20

Running queries which attempt to return too much data. 21

Schedule Query. 21

Select the XLS Format. 21

More Date Expressions. 22

Calculate the difference between two date-time fields. 22

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

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

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

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

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

First and Last day of month. 22

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

Built-in Functions. 25

 


 

Date Expressions


Calculate a person’s age

Expression: FLOOR(MONTHS_BETWEEN(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD') , 'YYYY-MM-DD'),TO_DATE('table name'.'birthdate field', 'YYYY-MM-DD'))/12)

 

2nd expression calculates age in months

 


Calculate years based on a date field

Expression: ((TO_DATE(SYSDATE)- TO_DATE(C.CMPNY_SENIORITY_DT,'YYYY-MM-DD'))/365.25)


 

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

 

Hardcoded Year End: MONTHS_BETWEEN ('2020-12-31', A.SERVICE_DT)/12

Current Year End: MONTHS_BETWEEN (TO_DATE('12-31-'||TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY'), A.SERVICE_DT)/12

 

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

 

The example below was used to return PTO entered to TL_PAYABLE_TIME for the coming week.

 

 

Suppress the birth year

Expression: TO_CHAR(BIRTHDATE,'fmMon, DD') returns Feb 28

If birthdate is not stored as a date format use:

Expression: TO_CHAR(TO_DATE (BIRTHDATE,'YYYY-MM-DD'), 'FmMON,DD')

 

Tenure in years based on Service Date

MONTHS_BETWEEN(SYSDATE,B.SERVICE_DT)/12

 

Return or use the last day of the current year

TO_DATE('12-31-'||TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY')

 


 

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 select the current year without hardcoding the value:

TO_CHAR(TO_DATE(SYSDATE,'YYYY-MM-DD'), 'YYYY')

 

To strip the Year from a date field

 

TO_CHAR(TO_DATE(A.CMPNY_SENIORITY_DT,'YYYY-MM-DD'), 'YYYY')

TO_CHAR(TO_DATE(B.DUR,'YYYY-MM-DD'), 'YYYY')

 

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

BETWEEN TO_DATE('01-01-'||TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY') and

TO_DATE('12-31-'||TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY')

 


CASE WHEN


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

Expression: Case When "field" = "condition" Then "return value or field" ELSE "if condition is not met value" End

To determine STD coverage amount – and cap at 1000.00

 

Complex Performance Rating Case When expression with calculations

CASE WHEN

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) * (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)

 

 


 

Excel Functions

 

Highlight alternating rows:

 

 

 

EXCEL – Calculates volume where returned amount ≤ 16667


 

Insert blank rows when key value changes