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

Posted on 2013-01-14
Medium Priority
Last Modified: 2013-01-21
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
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.

Question by:mjfagan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 14

Expert Comment

ID: 38775383
Can you show the exact output you wish?

Author Comment

ID: 38775674
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.
LVL 14

Expert Comment

ID: 38776237
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
DebitAmount <> 0 ) Lev1 )

Accepted Solution

mjfagan earned 0 total points
ID: 38784598
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
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

Author Closing Comment

ID: 38800527
I wound up figuring out the solution on my own but posted my solution in case others ran into the same sort of problem.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question