T-SQL MERGE statement--keeping unmatched "duplicate"

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
USING CompareTable
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.

Thanks.
LVL 6
Molly FaganApplications Team SupervisorAsked:
Who is Participating?
 
Molly FaganConnect With a Mentor Applications Team SupervisorAuthor Commented:
I had tried using sub selects before posting my question as I wasn't having any luck there.

I did wind up figuring it out myself and I'm going to post what I did just in case someone else runs into the same sort of problem.

I added a RecCount field to my CompareTable and populated the table with this query:

INSERT INTO CompareTable(GLAccountNo, FundNo, CreditAmount, TransID, RecCount)
SELECT GLAccountNo, FundNo, CreditAmount, TransID, COUNT(CreditAmount) AS RecCount
FROM SourceTable
WHERE CreditAmount > 0
GROUP BY GLAccountNo, FundNo, CreditAmount, TransID


Then to know how many records need to be marked as backouts, I did this:

MERGE INTO CompareTable
USING (SELECT GLAccountNo, FundNo, DebitAmount, TransID, COUNT(DebitAmount) AS RecCount
FROM SourceTable
WHERE DebitAmount > 0
GROUP BY GLAccountNo, FundNo, DebitAmount, TransID) AS ST
ON CompareTable.GLAccountNo = ST.GLAccountNo AND CompareTable.FundNo = ST.FundNo
AND CompareTable.CreditAmount = ST.DebitAmount AND CompareTable.TransID = ST.TransID
WHEN MATCHED AND (CompareTable.RecCount > ST.RecCount) THEN
UPDATE SET CompareTable.RecCount = ST.RecCount
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET CompareTable.RecCount = 0;

With the MATCHED and NOT MATCHED, I have situations where I have more credits than debits and vice-versa, plus no matching credits to debits so to avoid marking them as backouts, I set the RecCount = 0 so nothing would be updated.

Then in a sproc, I created a cursor (yes, I know but it does take less than 5 minutes to run and if someone knows of a way without the cursor at this point, I'd be happy to try it).

By using the CompareTable where the RecCount > 0, I loop through and do this within the cursor:

UPDATE TOP (@RecCount) SourceTable SET BackOut = 1
WHERE GLAccountNo = @GLAccountNo AND FundNo = @FundNo AND DebitAmount = @CreditAmount AND TransID = @TransID
                  
UPDATE TOP (@RecCount) SourceTable SET BackOut = 1
WHERE GLAccountNo = @GLAccountNo AND FundNo = @FundNo AND CreditAmount = @CreditAmount AND TransID = @TransID
0
 
EmesCommented:
Can you show the exact output you wish?
0
 
Molly FaganApplications Team SupervisorAuthor Commented:
GPID  GLAcct   FundNo   DebitAmount CreditAmount              Trans ID             BackOut
1        1010w      32xy             0.00                      8.53                     1234                1
2        1010w      32xy             0.00                      8.53                     1234                1
3        1010w      32xy             8.53                      0.00                     1234                0
4        1010w      32xy             8.53                      0.00                     1234                1
5        1010w      32xy             8.53                      0.00                     1234                1
6        1010w      32xy             4.00                      0.00                     1234                1
7        1010w      32xy             0.00                      4.00                     1234                1
8        1010w      32xy             5.15                      0.00                     1234                0

It doesn't matter which of the debit amounts (in this example), gets marked with a 1, it just matters that one of them remains a zero.
0
 
EmesCommented:
try using subselects like this


update ee1
set Blackout =1
where id in (
select id from (
select ee1.[id],ee1.FundNO,DebitAmount,ee1.TransId
from ee1
inner join (
select glacct ,FundNO,CreditAmont,TransId from ee1 ) F
on F.FundNO = ee1.FundNO
and f.CreditAmont = DebitAmount
and f.TransId =  ee1.TransId
where
DebitAmount <> 0 ) Lev1 )
0
 
Molly FaganApplications Team SupervisorAuthor Commented:
I wound up figuring out the solution on my own but posted my solution in case others ran into the same sort of problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.