Solved

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

Posted on 2013-01-14
5
391 Views
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
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.
0
Comment
Question by:mjfagan
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Emes
ID: 38775383
Can you show the exact output you wish?
0
 
LVL 6

Author Comment

by:mjfagan
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.
0
 
LVL 14

Expert Comment

by:Emes
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
where
DebitAmount <> 0 ) Lev1 )
0
 
LVL 6

Accepted Solution

by:
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
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
 
LVL 6

Author Closing Comment

by:mjfagan
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.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
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.

772 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