0

We have been inserting transaction payments incorrectly for the last 6 months. We need to insert broken down payments, but for some cases, we have been inserting payments summing them up.

How some payments were inserted incorrectly:

enter image description here

How the same transaction should have its payments inserted:

enter image description here

What I have done so far to identify the incorrect payments was to run the incorrect query and retrieve all the records starting on 12/01/2019, then save those records in a temporary table.

Query using the incorrect table:

SELECT DISTINCT
    CAST(P.ExternalId AS CHAR(25)) AS 'transID'
,   CASE RTP.Activity 
        WHEN 0 THEN 'Payment' 
        WHEN 1 THEN 'Void' 
    END AS 'transType'
,   P.TransactionCreateDate AS 'createdDate'
,   P.Amount AS 'paymentAmount'             <<< Payment column was being retrieved from wrong table.
,   P.EffectiveDate AS 'paymentDate'
,   CAST(P.Id AS NVARCHAR(50)) AS 'payment_Id'
,   P.TransactionBatchId AS 'batchId'
,   T.TenderTypeId AS 'TenderType'
,   CAST(TType.Name AS CHAR(10)) AS 'PaymentType'
FROM 
    Payments P
INNER JOIN dbo.Tenders T
    ON T.TransactionId = P.TransactionId
INNER JOIN dbo.TenderTypes TType
    ON TType.Id = T.TenderTypeId
WHERE 
    CAST(P.TransactionCreateDate AS DATE) >= '12/01/2019'
ORDER BY 
    createdDate DESC

Then I make corrections to the query, to retrieve all the transactions starting 12/01/2019 but getting the broken down payments this time, and putting them in a second temporary table. The difference are 955 records.

Query using the correct table to get payment amount:

SELECT DISTINCT
    CAST(P.ExternalId AS CHAR(25)) AS 'transID'
,   CASE RTP.Activity 
        WHEN 0 THEN 'Payment' 
        WHEN 1 THEN 'Void' 
    END AS 'transType'
,   P.TransactionCreateDate AS 'createdDate'
,   T.Amount AS 'paymentAmount'
,   P.EffectiveDate AS 'paymentDate'
,   CAST(P.Id AS NVARCHAR(50)) AS 'payment_Id'
,   P.TransactionBatchId AS 'batchId'
,   T.TenderTypeId AS 'TenderType'
,   CAST(TType.Name AS CHAR(10)) AS 'PaymentType'
FROM 
    Payments P
INNER JOIN dbo.Tenders T
    ON T.TransactionId = P.TransactionId
INNER JOIN dbo.TenderTypes TType
    ON TType.Id = T.TenderTypeId
WHERE 
    CAST(P.TransactionCreateDate AS DATE) >= '12/01/2019'
ORDER BY 
    createdDate DESC

My question is how can I now compare the contents of each table, to extract the ones that need to be corrected? I am sure this is not difficult, I am just overwhelmed and have not being able to figure it out and was wondering if someone could offer a helping hand.

Thank you much.

erasmo carlos
  • 664
  • 5
  • 16
  • 37
  • `EXCEPT`, `INTERSECT`? https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver15 – Max Zolotenko Dec 30 '19 at 21:26
  • https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver15 – Wilco Dec 30 '19 at 21:26
  • Can't you just delete the incorrect ones and then re-insert the correct data? – Nick Dec 30 '19 at 21:26
  • Does this answer your question? [sql query to return differences between two tables](https://stackoverflow.com/questions/2077807/sql-query-to-return-differences-between-two-tables) – Max Zolotenko Dec 30 '19 at 21:28

1 Answers1

2

Would the EXCEPT operator fix your issue?

SELECT A.a, A.b, ... 
FROM Table A  
EXCEPT  
SELECT B.a, B.b, ... 
FROM Table B;

Doc here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver15

Pierre
  • 159
  • 4