7.7 Relationship Manager & Rating - Data Issues

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:

WITH OverallActive AS (
    SELECT 
        "Account ID",
        "Readiness",
        "Date of Review" AS "Overall_Date",
        "Active",
        "Record Type"
    FROM 
        SALESFORCE_DB.TDA_JARVIS_DEV_SCH.RATING_VW_TEST1
    WHERE 
        "Record Type" = 'Overall Account Rating'
        AND "Active" = 'True'
),
NonOverallActive AS (
    SELECT 
        "Account ID",
        MAX("Date of Review") AS "Most_Recent_Non_Overall_Date"
    FROM 
        SALESFORCE_DB.TDA_JARVIS_DEV_SCH.RATING_VW_TEST1
    WHERE 
        "Record Type" <> 'Overall Account Rating'
        AND "Active" = 'True'
    GROUP BY 
        "Account ID"
)
SELECT 
    O."Account ID",
    O."Readiness" AS "Overall Readiness",
    O."Overall_Date",
    N."Most_Recent_Non_Overall_Date",
    CASE 
        WHEN N."Most_Recent_Non_Overall_Date" > O."Overall_Date" THEN 'More Recent Active Non-Overall Exists'
        ELSE 'No More Recent Active Non-Overall'
    END AS "Check Result"
FROM 
    OverallActive O
LEFT JOIN 
    NonOverallActive N
ON 
    O."Account ID" = N."Account ID"
WHERE "Check Result" = 'More Recent Active Non-Overall Exists';

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:

WITH RankedData AS (
    SELECT
        "Account ID",
        "Active",
        "Readiness",
        "Record Type",
        "Date of Review",
        LAG("Readiness") OVER (PARTITION BY "Account ID" ORDER BY "Date of Review") AS PrevReadiness,
        LEAD("Readiness") OVER (PARTITION BY "Account ID" ORDER BY "Date of Review") AS NextReadiness
    FROM 
        SALESFORCE_DB.TDA_JARVIS_DEV_SCH.RATING_VW_TEST1
    WHERE "Record Type" = 'Overall Account Rating'
)
SELECT
    "Account ID",
    "Active",
    "Readiness",
    "Record Type",
    "Date of Review",
    CASE 
        WHEN "Readiness" IS NULL AND PrevReadiness IS NOT NULL AND NextReadiness IS NOT NULL THEN 'Flag'
        ELSE NULL
    END AS "Flag"
FROM 
    RankedData
WHERE "Flag" = 'Flag';

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

WITH RankedPrimaryManagers AS (
    SELECT 
        A."ACCOUNT ID",
        A."USER" AS "Primary_Manager",
        A."STARTDATE",
        A."End Date Updated",
        ROW_NUMBER() OVER (
            PARTITION BY A."ACCOUNT ID"
            ORDER BY A."STARTDATE" DESC
        ) AS "Manager_Rank"
    FROM 
        SALESFORCE_DB.TDA_JARVIS_DEV_SCH.RELATIONSHIP_MANAGER_VW_1 A
    WHERE 
        A."ROLE" = 'Primary Manager'  -- Only consider Primary Manager role
        AND A."ACTIVE" = 'True'        -- Only consider active Primary Managers
)
SELECT 
    "ACCOUNT ID",
    "Primary_Manager",
    "STARTDATE",
    "End Date Updated"
FROM 
    RankedPrimaryManagers
WHERE 
    "Manager_Rank" = 1;

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.

WITH LaggedDates AS (
    SELECT
        "ACCOUNT ID",
        "USER",
        PRIMARY_MANAGER "Primary Manager",
        "ACTIVE",
        "STARTDATE",
        "End Date Updated",
        LAG("End Date Updated") OVER (
            PARTITION BY "ACCOUNT ID", "USER" 
            ORDER BY "STARTDATE" ASC NULLS LAST
        ) AS "Prev_End_Date"
    FROM 
        SALESFORCE_DB.TDA_JARVIS_DEV_SCH.RELATIONSHIP_MANAGER_VW_1
    WHERE 
        "Primary Manager" = 'Primary Manager'  -- Only consider Primary Manager role
),
GroupedManagers AS (
    SELECT
        "ACCOUNT ID",
        "USER",
        "Primary Manager",
        "ACTIVE",
        "STARTDATE",
        "End Date Updated",
        -- Create a new group when the current STARTDATE is greater than the previous row's End Date Updated
        SUM(
            CASE 
                WHEN "STARTDATE" > "Prev_End_Date" THEN 1
                ELSE 0 
            END
        ) OVER (PARTITION BY "ACCOUNT ID", "USER" ORDER BY "STARTDATE") AS "Group_Flag"
    FROM 
        LaggedDates
)
SELECT 
    "ACCOUNT ID",
    "USER",
    MIN("STARTDATE") AS "Consolidated_Start",
    MAX("End Date Updated") AS "Consolidated_End",
    "Primary Manager",
    "ACTIVE"
FROM 
    GroupedManagers
GROUP BY 
    "ACCOUNT ID", "USER", "Primary Manager", "ACTIVE", "Group_Flag"
ORDER BY 
    "ACCOUNT ID", "USER", "Consolidated_Start";;

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.