SQL query to compare column values from two tables and find mism

SQL query to compare column values from two tables and find mismatches

I have below query which I am using to compare some column values between two tables. The issue is I am getting many duplicate rows of mismatch values where against a particular column value. In my below query I am comparing column values of 'IsActive', 'Scheme' and 'routing' against the column value for 'AgentRepresentation'. Such that if the value of one of these column does not matches then I get a customized Reconciliation Status. Also, I am checking whether this column 'AgentRepresentation' is missing in either tables or not.

SELECT 
    CASE 
        WHEN fcubs.AgentRepresentation IS NULL THEN 'missing in fcubs'
        WHEN cepos.AgentRepresentation IS NULL THEN 'missing in cepos'
        WHEN fcubs.IsActive <> cepos.IsActive THEN 'IsActive mismatch'
        WHEN fcubs.Scheme <> cepos.Scheme THEN 'Scheme mismatch'
        WHEN fcubs.Routing <> cepos.Routing THEN 'Routing mismatch'
        ELSE 'Unknown condition'
    END AS ReconciliationStatus,
    COALESCE(fcubs.AgentRepresentation, cepos.AgentRepresentation) AS AgentRepresentation,
    fcubs.IsActive AS fcubsIsActive,
    cepos.IsActive AS ceposIsActive,
    fcubs.Scheme AS fcubsScheme,
    cepos.Scheme AS ceposScheme,
    fcubs.Routing AS fcubsRouting,
    cepos.Routing AS ceposRouting
FROM [cpe/NetworkReachability/fcubs-NetworkReachability] fcubs
Full Outer Join [cpe/NetworkReachability/cepos-NetworkReachability] cepos ON fcubs.AgentRepresentation= cepos.AgentRepresentation
WHERE fcubs.AgentRepresentation IS NULL -- Records not in fcubs
   OR cepos.AgentRepresentation IS NULL -- Records not in cepos
   OR 
   Not(fcubs.IsActive = cepos.IsActive -- Records with mismatched IsActive value
   And fcubs.Scheme = cepos.Scheme -- Records with mismatched Scheme value
   And fcubs.Routing = cepos.Routing) -- Records with mismatched Routing value
order by AgentRepresentation

Answers

It seems like you're encountering duplicate rows because your query is checking for mismatches in multiple columns independently. As a result, if there are multiple rows in either table where the AgentRepresentation value is the same but other columns have different values, you'll get duplicate rows in the result set.

To address this issue and get a cleaner result set with one row per AgentRepresentation value, you can use a subquery to identify rows with mismatched values and then perform the reconciliation status check on the aggregated result.

Here's an updated version of your query that should help to reduce duplicate rows:

WITH MismatchedRows AS (
    SELECT 
        COALESCE(fcubs.AgentRepresentation, cepos.AgentRepresentation) AS AgentRepresentation,
        fcubs.IsActive AS fcubsIsActive,
        cepos.IsActive AS ceposIsActive,
        fcubs.Scheme AS fcubsScheme,
        cepos.Scheme AS ceposScheme,
        fcubs.Routing AS fcubsRouting,
        cepos.Routing AS ceposRouting
    FROM [cpe/NetworkReachability/fcubs-NetworkReachability] fcubs
    FULL OUTER JOIN [cpe/NetworkReachability/cepos-NetworkReachability] cepos 
    ON fcubs.AgentRepresentation = cepos.AgentRepresentation
    WHERE fcubs.AgentRepresentation IS NULL
       OR cepos.AgentRepresentation IS NULL
       OR fcubs.IsActive <> cepos.IsActive
       OR fcubs.Scheme <> cepos.Scheme
       OR fcubs.Routing <> cepos.Routing
)
SELECT 
    CASE 
        WHEN AgentRepresentation IS NULL THEN 'missing in fcubs'
        WHEN ceposIsActive IS NULL THEN 'missing in cepos'
        WHEN fcubsIsActive <> ceposIsActive THEN 'IsActive mismatch'
        WHEN fcubsScheme <> ceposScheme THEN 'Scheme mismatch'
        WHEN fcubsRouting <> ceposRouting THEN 'Routing mismatch'
        ELSE 'Unknown condition'
    END AS ReconciliationStatus,
    AgentRepresentation,
    fcubsIsActive,
    ceposIsActive,
    fcubsScheme,
    ceposScheme,
    fcubsRouting,
    ceposRouting
FROM MismatchedRows
ORDER BY AgentRepresentation;

In this query:

• We use a Common Table Expression (CTE) called MismatchedRows to identify rows where there are mismatches in any of the specified columns or where the AgentRepresentation is missing in either table.
• Then, in the main query, we perform the reconciliation status check on the aggregated result of the CTE.
• This approach should help to reduce duplicate rows in the result set by consolidating rows with the same AgentRepresentation value.