SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_TY = '%LOGGEDINUSERID%'
it becomes:
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_TY = 'JOSH'
'%LOGGEDINEMPCODE%' - Returns the currently logged in user's Employee ID, which is found in the EM_EMPL_CD field in the WKUEMP table. When used in a filter, the system replaces the reserved word with the Work Employee Code of the currently logged in user.
Requirements - An agency must own the Work module to use this string. Also, the logged in employee must have his or her Login ID associated with an Employee record in the Work Flow Setup > Employees module.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%FISCALYEAR%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '01/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '01/01/2015 12:00 AM'
'%CURRENTYEAR%' - Inserts the current calendar year.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTYEAR%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '2014-01-01' and EFEQUIP.GE_CUR_DT < '2015-01-01'
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTQUARTER%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.GE_FL_ID = EFFLEET.FL_ID WHERE EFEQUIP.ge_cur_dt >= '04/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
'%CURRENTWEEK%' - Looks at the current date and inserts the dates for the Sundays before and after the current date.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/29/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/06/2014 12:00 AM'
'%CURRENTWEEK1%' - Looks at the current date and inserts the dates for the Sundays before and after the current date. (Results are identical to those using the previous reserve word.)
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK1%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/29/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/06/2014 12:00 AM'
'%CURRENTWEEK2%' - Looks at the current date and inserts the dates for the Monday in the same calendar week and the next Monday.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK2%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/30/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/07/2014 12:00 AM'
In this example, the user runs the filter on a Monday; so, the system returns records from the "previous" Monday (12 a.m. that same day, 06/30) to 12 a.m. the following Tuesday,07/07 (which catches all of Monday, 7/6).
'%CURRENTWEEK3%' - Looks at the current date and inserts the dates for the Tuesday in the same calendar week and the next Tuesday.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK3%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/08/2014 12:00 AM'
'%CURRENTWEEK4%' - Looks at the current date and inserts the dates for the Wednesday in the same calendar week and the next Wednesday.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK4%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/02/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/09/2014 12:00 AM'
'%CURRENTWEEK5%' - Looks at the current date and inserts the dates for the Thursday in the same calendar week and the next Thursday.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK5%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/03/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/10/2014 12:00 AM'
'%CURRENTWEEK6%' - Looks at the current date and inserts the dates for the Friday in the same calendar week and the next Friday.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK6%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/04/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/11/2014 12:00 AM'
'%CURRENTWEEK7%' - Looks at the current date and inserts the dates for the Saturday in the same calendar week and the next Saturday.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK7%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/05/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/12/2014 12:00 AM'
'%CURRENTDAY%' - Inserts the current 24-hour period. This is a range from 12:00 AM of the current day to 12:00 AM of the next day.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTDAY%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/28/2014 12:00 AM'
'%PREVIOUSYEAR%' - Looks at the current date and inserts January 1 of the previous year to January 1 of the current year.
On Friday, 6/27/2014, a user runs the following filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSYEAR%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '01/01/2013 12:00 AM' AND EFEQUIP.ge_cur_dt < '01/01/2014 12:00 AM'
'%PREVIOUSMONTH%' - Looks at the current date and inserts the first date of the previous month and the first date of the current month.
On Friday, 6/27/2014, a user runs the following filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '05/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/01/2014 12:00 AM'
'%PREVIOUSWEEK%' - Looks at the current date and inserts the dates of the previous week, Sunday to Sunday.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSWEEK%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/15/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/22/2014 12:00 AM'
'%PREVIOUSDAY%' - Inserts the day previous to the current day.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSDAY%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/26/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/27/2014 12:00 AM'
'%FIRSTHALFOFTHEMONTH%' - Inserts the first half of the month (days 1-15).
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%FIRSTHALFOFTHEMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/16/2014 12:00 AM'
'%SECONDHALFOFTHEMONTH%' - Inserts the second half of the month (days 16-31).
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%SECONDHALFOFTHEMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/16/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
'%ONEWEEKAHEAD%' - Inserts the current day, plus seven future days.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%ONEWEEKAHEAD%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/05/2014 12:00 AM'
'%TWOWEEKSAHEAD%' - Inserts the plus 14 future days.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%TWOWEEKSAHEAD%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM'' AND EFEQUIP.ge_cur_dt < ''07/12/2014 12:00 AM'
'%ONEWEEKAHEADANDBEFORE%' - Inserts code to encompass all previous time, plus one week in the future. For example, this string could be used to query for all existing open Work Orders, as well as those that are open/due in the next week.
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%TWOWEEKSAHEADANDBEFORE%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt <= '07/15/2014 12:00 AM'
'%LAST(X,Y)%' - Inserts a range of dates where the Last Date is calculated as the Current Date - X days and the First Date is calculated as the Last Date - Y days.
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '%LAST(1,5)%'
It becomes:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt >= '6/21/2014' AND WKORDER.wo_mod_dt <= '6/26/2014'
The current date is 6/27. The reserve word goes back 1 days to 6/26 to find the end of the date range. It then goes back 5 days to find the beginning of the date range, 6/21.
'%NEXT(X,Y)%' - Inserts a range of dates where the First Date is calculated as the Current Date + X days and the Last Date is calculated as the Current Date + Y days.
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '%NEXT(0,10)%'
It becomes:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt >= '6/30/2014' AND WKORDER.wo_mod_dt <= '7/10/2014'
The current date is 6/30. The reserve word goes forward 0 days to find a beginning date of 6/30. It then goes forward 10 days to find an end date of 7/10.