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.7 Relationship Manager & Rating - Data Issues
- natalia.bashkova
Table of Contents:
Purpose
Determine data quality issues within the RELATIONSHIP_MANAGER_VW (RM) and RATING_VW (R).
1. Data quality issues
1.1 Overlapping Dates per Account (RM)
Example:
WITH Overlaps AS (
SELECT
A."ACCOUNT ID",
A."USER" AS "User_A",
B."USER" AS "User_B",
A."STARTDATE" AS "Start_A",
COALESCE(A."ENDDATE", CURRENT_DATE) AS "End_A",
B."STARTDATE" AS "Start_B",
COALESCE(B."ENDDATE", CURRENT_DATE) AS "End_B",
GREATEST(A."STARTDATE", B."STARTDATE") AS "Overlap_Start",
LEAST(COALESCE(COALESCE(A."ENDDATE", CURRENT_DATE), CURRENT_DATE), COALESCE(COALESCE(B."ENDDATE", CURRENT_DATE), CURRENT_DATE)) AS "Overlap_End"
FROM
SALESFORCE_DB.TDA_JARVIS_DEV_SCH.RELATIONSHIP_MANAGER_VW A
JOIN
SALESFORCE_DB.TDA_JARVIS_DEV_SCH.RELATIONSHIP_MANAGER_VW B
ON
A."ACCOUNT ID" = B."ACCOUNT ID" -- Same account
AND A."USER" <= B."USER" -- Prevent double-checking
AND A."PRIMARY_MANAGER" = 'Primary Manager'
AND B."PRIMARY_MANAGER" = 'Primary Manager'
AND (
(A."STARTDATE" < COALESCE(B."ENDDATE", CURRENT_DATE)
AND COALESCE(A."ENDDATE", CURRENT_DATE) > B."STARTDATE")
)
)
SELECT
"ACCOUNT ID",
"User_A" AS "User Name",
"Start_A" AS "STARTDATE",
"End_A" AS "End Date Updated",
"User_B" AS "Overlapping User",
"Start_B" AS "Overlapping Start Date",
"End_B" AS "Overlapping End Date",
"Overlap_Start",
"Overlap_End",
DATEDIFF(day, "Overlap_Start", "Overlap_End") AS "Days of Overlap" -- Calculate the number of overlap days
FROM
Overlaps
WHERE
"User_A" <> "User_B";
Output:
Provides Overlapping User, Overlapping Start/ End date per Account ID.
Provides no. of days overlap.
1.2 Multiple Active Managers (RM)
Example:
WITH ActivePrimaryManagers AS (
SELECT
A."ACCOUNT ID",
A."USER" AS "User_A",
B."USER" AS "User_B",
A."STARTDATE" AS "Start_A",
A."End Date Updated" AS "End_A",
B."STARTDATE" AS "Start_B",
B."End Date Updated" AS "End_B",
GREATEST(A."STARTDATE", B."STARTDATE") AS "Overlap_Start",
LEAST(A."End Date Updated", B."End Date Updated") AS "Overlap_End",
DATEDIFF(day, GREATEST(A."STARTDATE", B."STARTDATE"), LEAST(A."End Date Updated", B."End Date Updated")) AS "Days of Overlap"
FROM
SALESFORCE_DB.TDA_JARVIS_DEV_SCH.RELATIONSHIP_MANAGER_VW_1 A
JOIN
SALESFORCE_DB.TDA_JARVIS_DEV_SCH.RELATIONSHIP_MANAGER_VW_1 B
ON
A."ACCOUNT ID" = B."ACCOUNT ID" -- Same account
AND A."USER" < B."USER" -- Prevent double-checking and avoid self-join
AND A."ROLE" = 'Primary Manager' -- Only consider Primary Manager role
AND B."ROLE" = 'Primary Manager' -- Only consider Primary Manager role
AND A."ACTIVE" = 'True' -- Only consider active Primary Managers
AND B."ACTIVE" = 'True' -- Only consider active Primary Managers
AND (
(A."STARTDATE" <= B."End Date Updated"
AND A."End Date Updated" >= B."STARTDATE")
)
)
SELECT
"ACCOUNT ID",
"User_A" AS "Primary Manager 1",
"User_B" AS "Primary Manager 2",
"Overlap_Start" AS "Overlap Start Date",
"Overlap_End" AS "Overlap End Date",
"Days of Overlap"
FROM
ActivePrimaryManagers
WHERE
"Days of Overlap" > 0;
Output:
Provides overlapping Primary Managers IDs, overlapping dates and no. of days overlap.
1.3 Multiple overall active ratings (R)
Example:
WITH ActiveReadiness AS (
SELECT
A."Account ID",
A."Readiness" AS "Readiness_A",
B."Readiness" AS "Readiness_B",
A."Date of Review" AS "Date_A",
B."Date of Review" AS "Date_B",
DATEDIFF(day, A."Date of Review", B."Date of Review") AS "Days_Between"
FROM
SALESFORCE_DB.TDA_JARVIS_DEV_SCH.RATING_VW_TEST1 A
JOIN
SALESFORCE_DB.TDA_JARVIS_DEV_SCH.RATING_VW_TEST1 B
ON
A."Account ID" = B."Account ID" -- Same account
AND A."Readiness" <> B."Readiness" -- Different readiness states
AND A."Active" = 'True' -- Only consider active readiness
AND B."Active" = 'True'
AND YEAR(A."Date of Review") >= 2021 -- Filter for Date of Review Year >= 2021
AND YEAR(B."Date of Review") >= 2021
AND A."Record Type" = 'Overall Account Rating' -- Filter for Record Type = 'Overall Account Rating'
AND B."Record Type" = 'Overall Account Rating'
AND (
(A."Date of Review" <= B."Date of Review")
)
)
SELECT
"Account ID",
"Readiness_A" AS "Readiness 1",
"Readiness_B" AS "Readiness 2",
"Date_A" AS "Review Date 1",
"Date_B" AS "Review Date 2",
"Days_Between" AS "Days Between Reviews"
FROM
ActiveReadiness
WHERE
"Days_Between" >= 0;
Output:
Provides overlapping active readiness and no. of days overlap per Account ID.
1.4 Readiness with an active overall account rating is not with the most recent ‘Date of Review’ (R)
Example:
Output:
flag ‘More Recent Active Non-Overall Exists’ indicates that there is an active non overall account rating with a more recent Date of Review.
1.5 Accounts with blank readiness between filled readiness (R)
Example:
Output:
Provides all accounts with blank readiness between filled readiness.
Note: blank readiness with the same date as filled readiness also gets picked up.
2. Handling data quality issues
2.1 Handling multiple active managers
Rank active primary managers per account based on their start dates
If there are multiple active primary managers - pick the one with the most recent start date
2.2 Handling overlapping primary managers
if primary managers (not necessarily active) have multiple rows with consecutive dates, take the earliest start date per user and the latest end date per that user within an account to get one row per primary manager.
Example:
Peta and Nalan will both have one row, with start and end dates as 15/02/2019 - 07/04/2021 and 15/02/2019 - 21/12/2022 accordingly.