?
Solved

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

Posted on 2013-01-14
5
Medium Priority
?
423 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:Molly Fagan
  • 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:Molly Fagan
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:
Molly Fagan 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:Molly Fagan
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

809 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