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

Posted on 2013-01-14
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
  • 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In an interesting question ( here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 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