Eliminating non matching rows dependent on specific columns between two tables using SQL-CodePudding

I have two tables where I want to match only on specific columns such as finding matching account numbers. I have that portion figured out however two of the columns are calltime and calldate. I want to find all records that are a discrepancy (found in one but not the other). The issue is that there may be multiple calls in a day so the below is not working to find discrepancies.

Query 1 to get all calls where call date matches but is not found. The issue with this is that there may be a match for call date and call time but it's just not the current record being compared

SELECT *
FROM dbo.VDMTable
WHERE EXISTS (SELECT * 
              FROM DataopsTable
              WHERE DataopsTable.Citibank = VDMTable.Citiaccount
                AND DataopsTable.CallDate = VDMTable.Calldate
                AND DataopsTable.CallTime <> VDMTable.Calltime

CodePudding user response：

You are looking for rows where exists a mismatch, and as you say, such mismatches are not really what you are looking for. What you are really interested in are rows for which not exists a match.

FROM dbo.VDMTable
WHERE NOT EXISTS
  (
    SELECT null
    FROM DataopsTable
    WHERE DataopsTable.Citibank = VDMTable.Citiaccount
    AND DataopsTable.CallDate = VDMTable.Calldate
    AND DataopsTable.CallTime = VDMTable.Calltime
)

CodePudding user response：

To find discrepancies between the two tables based on the call date and call time columns, you can use a LEFT JOIN to join the two tables and then filter the results to only include rows where the call time is not null in one table but null in the other.

Here's an example of how you can do this:

SELECT *
FROM dbo.VDMTable t1
LEFT JOIN dbo.DataopsTable t2
ON t1.Citiaccount = t2.Citibank
AND t1.Calldate = t2.CallDate
AND t1.Calltime = t2.CallTime
WHERE (t1.Calltime IS NOT NULL AND t2.CallTime IS NULL)
OR (t1.Calltime IS NULL AND t2.CallTime IS NOT NULL)

This query will return all rows from the VDMTable that have a matching Citiaccount and CallDate in the DataopsTable, but have a different CallTime value. It will also return all rows from the DataopsTable that have a matching Citiaccount and CallDate in the VDMTable, but have a different CallTime value.