T-SQL MERGE statement--keeping unmatched "duplicate"
Posted on 2013-01-14
I'm working on an accounting problem and trying to "cancel out" matching debits and credits but keeping any unmatched.
For example, the SourceTable has data that looks like this:
GPID GLAcct FundNo DebitAmount CreditAmount Trans ID BackOut
1 1010w 32xy 0.00 8.53 1234 0
2 1010w 32xy 0.00 8.53 1234 0
3 1010w 32xy 8.53 0.00 1234 0
4 1010w 32xy 8.53 0.00 1234 0
5 1010w 32xy 8.53 0.00 1234 0
6 1010w 32xy 4.00 0.00 1234 0
7 1010w 32xy 0.00 4.00 1234 0
8 1010w 32xy 5.15 0.00 1234 0
What the accountants want is to have appropriate debits and credits cancel each other out based on match GLAcct, FundNo, and TransID (yes, there are different GLAcct, FundNo, and TransID but this is a generic sample and this sample mimics the live data in that within the GLAcct, FundNo, and TransID, there are different amounts). I'm using the BackOut field as a flag to indicate that a record is cancelled out by another. So I'm trying to figure out a way to mark all but the row with a debit of 5.15 and one of the rows with a debit of 8.53 as "backouts" because they cancel each other out but those rows do not have a matching credit (and conversely, it might be a credit value that doesn't have a matching debit.)
I took all of the records with a credit > 0 and dumped into a CompareTable and tried the following MERGE statements:
MERGE INTO SourceTable
USING (SELECT MAX(GPID) AS GPID, GLAccountNo, FundNo, CreditAmount, TransID FROM CompareTable
GROUP BY GLAccountNo, FundNo, CreditAmount, TransID) AS CT
ON SourceTable.GLAccountNo = CT.GLAccountNo AND SourceTable.FundNo = CT.FundNo
AND SourceTable.DebitAmount = CT.CreditAmount AND SourceTable.TransID = CT.TransID
WHEN MATCHED AND (SourceTable.Backout = 0) THEN
UPDATE SET SourceTable.BackOut = 1;
MERGE INTO CompareTable
USING (SELECT MAX(GPID) AS GPID, GLAccountNo, FundNo, DebitAmount, TransID FROM SourceTable
GROUP BY GLAccountNo, FundNo, DebitAmount, TransID) AS CT
ON CompareTable.GLAccountNo = CT.GLAccountNo AND CompareTable.FundNo = CT.FundNo
AND CompareTable.CreditAmount = CT.DebitAmount AND CompareTable.TransID = CT.TransID
WHEN MATCHED AND (CompareTable.Backout = 0) THEN
UPDATE SET CompareTable.BackOut = 1;
MERGE INTO SourceTable
ON SourceTable.GPID = CompareTable.GPID
WHEN MATCHED THEN
UPDATE SET SourceTable.BackOut = CompareTable.Backout;
The result is that all of the rows except for the one with a 5.15 value gets marked as a backout. Is there anyway of getting this data appropriately marked?
Yes, I've tried the dreaded cursor but the actual live data is almost a million rows and I couldn't get any results in a a reasonable amount of time.
Any help would be appreciated.