Needing help with syntax for a Date filter

Kevin Robertson

Hi,

We picked up an old catalog file report from CC to do with Assignment Change. Unfortunately when we load it into our catalog it doesnt recognise the Subject area. However we can still run it somewhat to our needs but we just cant edit it properly. We would like to hard code a filter so it looks at returning data on the new_action_date from 1 previous month from the current date that its run. Unfortunately every syntax we try to use isn't being recognised when we upload it via the Advanced/New Analysis option.

This is the code:

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1',PREFERRED_CURRENCY='User Preferred Currency 1';SELECT
OuterQuery.Person_Number saw_0,
OuterQuery.Employee_Name saw_1,
OuterQuery.Assignment_Last_Update_Date saw_2,
OuterQuery.Assignment_Last_Updated_By saw_3,
OuterQuery.Old_Action_Name saw_4,
OuterQuery.New_Action_Name saw_5,
OuterQuery.Old_Action_date saw_6,
OuterQuery.New_Action_Date saw_7,
OuterQuery.Old_Business_Unit saw_8,
OuterQuery.New_Business_Unit saw_9,
OuterQuery.Old_Department saw_10,
OuterQuery.New_Department saw_11,
OuterQuery.Old_Grade saw_12,
OuterQuery.New_Grade saw_13,
OuterQuery.Old_Job saw_14,
OuterQuery.New_Job saw_15,
OuterQuery.Old_Location saw_16,
OuterQuery.New_Location saw_17,
OuterQuery.Old_Position saw_18,
OuterQuery.New_Position saw_19
FROM (
SELECT
New.New_Person_Number Person_Number,
New.New_Employee_Name Employee_Name,
New.New_Assignment_Last_Update_Date Assignment_Last_Update_Date,
New.New_Assignment_Last_Updated_By Assignment_Last_Updated_By,
New.New_Action_Name New_Action_Name,
Old.Old_Action_Name Old_Action_Name,
New.New_Action_Date New_Action_Date,
Old.Old_Action_date Old_Action_Date,
New.New_Business_Unit New_Business_Unit,
Old.Old_Business_Unit Old_Business_Unit,
New.New_Department New_Department,
Old.Old_Department Old_Department,
New.New_Grade New_Grade,
Old.Old_Grade Old_Grade,
New.New_Job New_Job,
Old.Old_Job Old_Job,
New.New_Location New_Location,
Old.Old_Location Old_Location,
New.New_Position New_Position,
Old.Old_Position Old_Position
FROM
(
SELECT
"Workforce Management - Worker Assignment Event Real Time"."Worker"."Person Number" New_Person_Number,
"Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Name" New_Employee_Name,
"Workforce Management - Worker Assignment Event Real Time"."Time"."Date" New_Action_Date,
"Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Assignment Sequence" New_Assignment_Sequence,
"Workforce Management - Worker Assignment Event Real Time"."HR Action"."Action Name" New_Action_Name,RCOUNT(1) New_Row_Count,
"Workforce Management - Worker Assignment Event Real Time"."Business Unit"."Business Unit Name" New_Business_Unit,
"Workforce Management - Worker Assignment Event Real Time"."Department"."Department Name" New_Department,
"Workforce Management - Worker Assignment Event Real Time"."Grade"."Grade Name" New_Grade,
"Workforce Management - Worker Assignment Event Real Time"."Job"."Job Name" New_Job,
"Workforce Management - Worker Assignment Event Real Time"."Location"."Worker Location Name" New_Location,
"Workforce Management - Worker Assignment Event Real Time"."Position"."Position Name" New_Position,
"Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Assignment Last Update Date" New_Assignment_Last_Update_Date,
"Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Assignment Last Updated By" New_Assignment_Last_Updated_By
FROM "Workforce Management - Worker Assignment Event Real Time"
WHERE TOPN(RCOUNT("Worker"."Person Number"),2) <= 2
AND "Position"."Position Name" = 'Pupil Support Assistant Level 2'
GROUP BY "Worker"."Person Number"
) New,
(
SELECT
"Workforce Management - Worker Assignment Event Real Time"."Worker"."Person Number" Old_Person_Number,
"Workforce Management - Worker Assignment Event Real Time"."Time"."Date" Old_Action_Date,
"Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Assignment Sequence" Old_Assignment_Sequence,
"Workforce Management - Worker Assignment Event Real Time"."HR Action"."Action Name" Old_Action_Name,
RCOUNT(1) Old_Row_Count,
"Workforce Management - Worker Assignment Event Real Time"."Business Unit"."Business Unit Name" Old_Business_Unit,
"Workforce Management - Worker Assignment Event Real Time"."Department"."Department Name" Old_Department,
"Workforce Management - Worker Assignment Event Real Time"."Grade"."Grade Name" Old_Grade,
"Workforce Management - Worker Assignment Event Real Time"."Job"."Job Name" Old_Job,
"Workforce Management - Worker Assignment Event Real Time"."Location"."Worker Location Name" Old_Location,
"Workforce Management - Worker Assignment Event Real Time"."Position"."Position Name" Old_Position,
"Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Assignment Last Update Date" Old_Assignment_Last_Update_Date,
"Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Assignment Last Updated By" Old_Assignment_Last_Updated_By
FROM "Workforce Management - Worker Assignment Event Real Time"
WHERE TOPN(RCOUNT("Worker"."Person Number"),2) <= 2
GROUP BY "Worker"."Person Number"
) Old
WHERE New.New_Row_Count = (Old.Old_Row_Count+1)
AND New.New_Person_Number = Old.Old_Person_Number
AND (New.New_Department <> Old.Old_Department
OR New.New_Grade <> Old.Old_Grade
OR New.New_Job <> Old.Old_Job
OR New.New_Position <> Old.Old_Position
OR New.New_Location <> Old.Old_Location)
) OuterQuery
ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4, saw_5, saw_6, saw_7, saw_8, saw_9, saw_10, saw_11, saw_12, saw_13, saw_14, saw_15, saw_16, saw_17, saw_18, saw_19

Thank you,

Kevin.

Kevin Robertson

Resolved our issue, please ignore.