/
7.8 HR Data Issues
Attention: Confluence is not suitable for the storage of highly confidential data. Please ensure that any data classified as Highly Protected is stored using a more secure platform.
If you have any questions, please refer to the University's data classification guide or contact ict.askcyber@sydney.edu.au
7.8 HR Data Issues
- natalia.bashkova
Owned by natalia.bashkova
Multiple Managers Data
select
"Full Name",
"Employee No",
UNIKEY,
"Position No",
"Position Title",
"Start Date",
"End Date Year",
"Calendar Year",
manager_name_report,
manager_employee_no,
manager_position_no
from sp_advancement_db.tda_sch.hr_tap_data_yearly_vw_test_ys
where contains(manager_name_report, ';') and "Calendar Year" >= 2019;Example:
Missing Manager Data
select
"Full Name",
"Employee No",
UNIKEY,
"Position No",
"Position Title",
"Start Date",
"End Date Year",
"Calendar Year",
manager_name_report,
manager_employee_no,
manager_position_no
from sp_advancement_db.tda_sch.hr_tap_data_yearly_vw_test_ys
where "Calendar Year" >= 2019 and
((manager_name_report = '' or manager_name_report is null) or (manager_position_no is null or manager_position_no = ''))
;
Example:
Full Name | Employee No | UNIKEY | Position No | Position Title | Start Date | End Date Year | Calendar Year | MANAGER_NAME_REPORT | MANAGER_EMPLOYEE_NO | MANAGER_POSITION_NO |
Monckton, Dominic Walter | 1166716 | DMON9393 | 44251 | Development Associate | 2020-06-19 | 2020-09-15 | 2020 |
|
| 41440 |
Mansfield, Angela Claire | 1123280 | ARAVEN | 29677 | Annual Giving Specialist | 2020-06-19 | 2021-03-07 | 2021 |
|
| 0078916 |
Mansfield, Angela Claire | 1123280 | ARAVEN | 29677 | Annual Giving Specialist | 2020-06-19 | 2020-12-31 | 2020 |
|
| 0078916 |
Magee, Peta | 1154354 | PMAG8636 | 38724 | Director of Development University Multidisciplinary Initiatives | 2021-07-29 | 2021-08-01 | 2021 |
|
| 26359 |
Overlapping Manager Positions (Check)
WITH AggregatedDates AS (
SELECT
MANAGER_NAME_REPORT,
MANAGER_EMPLOYEE_NO AS Manager_Employee_No,
MANAGER_POSITION_NO AS Manager_Position_No,
MIN("Start Date") AS Earliest_Start_Date,
MAX("End Date") AS Latest_End_Date
FROM
sp_advancement_db.tda_sch.hr_base_do_vw
GROUP BY
MANAGER_NAME_REPORT,
MANAGER_EMPLOYEE_NO,
MANAGER_POSITION_NO
),
Overlaps AS (
SELECT
t1.manager_name_report,
t1.manager_employee_no,
t1.manager_position_no AS Position1,
t2.manager_position_no AS Position2,
t1.Earliest_Start_Date AS Start1,
t1.Latest_End_Date AS End1,
t2.Earliest_Start_Date AS Start2,
t2.Latest_End_Date AS End2
FROM
AggregatedDates t1
JOIN
AggregatedDates t2
ON
t1.manager_employee_no = t2.manager_employee_no
AND t1.manager_position_no <> t2.manager_position_no
AND t1.Earliest_Start_Date <= t2.Latest_End_Date
AND t1.Latest_End_Date >= t2.Earliest_Start_Date
)
SELECT *
FROM Overlaps
order by manager_name_report, position1, position2;
Example:
Overlapping Employee Positions (Check)
Example:
note there is also a miss match between dates for positions per person
, multiple selections available,