Match Canceling Records

Question

Match Canceling Records

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

This is the nearest I could come up with - it works with your sample data but you'll need to try this with a larger data set.

WITH CTE AS (

SELECT t1.AcctNum, t1.SeqNum, t1.TranType, t1.TranAmt, t1.TranPeriod,

t2.SeqNum AS MatchingSeqNum,

-- Prioritise the matching cancelling accounts, populated date first followed by nearest SeqNum

ROW_NUMBER() OVER(PARTITION BY t1.AcctNum, t1.SeqNum

ORDER BY CASE WHEN t2.TranPeriod<>0 AND t2.TranPeriod=t1.TranPeriod THEN 0 ELSE 1 END,

ABS(t2.SeqNum-t1.SeqNum),

SIGN(t2.SeqNum-t1.SeqNum)) AS rn

FROM #TmpData t1

-- Join to all other candidate cancelling accounts

LEFT OUTER JOIN #TmpData t2 ON t2.AcctNum=t1.AcctNum

AND t2.TranType<>t1.TranType

AND t2.TranAmt+t1.TranAmt=0

)

SELECT AcctNum, SeqNum, TranType, TranAmt, TranPeriod,

-- If there are multiple matches, use the "nearest" only

CASE WHEN ROW_NUMBER() OVER(PARTITION BY AcctNum,MatchingSeqNum ORDER BY ABS(MatchingSeqNum-SeqNum))=1

THEN CAST(MatchingSeqNum AS VARCHAR(10)) ELSE 'Unmatched' END AS MatchingSeqNum

FROM CTE

WHERE rn=1

ORDER BY AcctNum, SeqNum;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 2

DougG (1/23/2012)
Thanks for the response.
Let me see if I can clear it up a bit:
TranSeqMatching TranSeq
1Unmatched
25
34
43
52
67
76
89
98
1011
1110
12Unmatched

Already with this you've violated your theory constraints. Your comment "When there is a matching period date for both transaction types and the amounts cancel, they should be considered a match." indicates that matches should not be allowed for data that does not have a TranPeriod, but your result set matches up sequence numbers 5 & 2.

You need to be aware of what will happen to your data if you allow sloppy business rules to work their way into your code. Allowing a Null (or 0 in this case) TranPeriod to be matched up with a specific TranPeriod will give you false matches.

Going back to the beginning, what I'm reading is:

I have issues and I have cancels. Not every issue will have a cancel, but if there is a cancel, I want to negate the whole thing by zeroing it out or removing both issue and cancel from my record set. This needs to be done within the same Account Number (matching from different account numbers will NOT be done). Each transaction has a unique sequence number and a transaction type of either Issue (trantype 1) or Cancel (trantype 2). If an Issue & Cancel both have the same TranPeriod, that is the criteria by which they shall be canceled.

Is the above a correct statement?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

DougG SSChasing Mays Points: 612 More actions · Answer 3

Thanks Mark.

I will give it a go and let you know.

Can you provide some details about how this works?

I would like to understand the concept, so I can do this type thing in the future.

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 4

Reading further, it looks like you want to take the first Cancel of several as a match.

Which makes me curious. How are multiple cancels for one issue making it into your transaction system? What is the business logic allowing for this?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

DougG SSChasing Mays Points: 612 More actions · Answer 5

Both TranType 1 and TranType 2 are valid transactions on there own or are used to reverse the opposite transaction.

The way the system works:

Using the sample data provided, assume only SeqNum 1 - 3 have occurred.

AcctNumSeqNumTranTypeTranAmtTranPeriod

1111020110101

1211020110201

131150

If I only want to reverse SeqNum 2, the system will reverse SeqNum 3 and SeqNum 2.

Thus we have SeqNums 4 and 5.

AcctNumSeqNumTranTypeTranAmtTranPeriod

1111020110101

1211020110201

131150

142-150

152-100

Now since I only really wanted to reverse SeqNum 2, I would then have to enter a new transaction that would look exactly like SeqNum 3.

This would be SeqNum 6 for this scenario (not the same as SeqNum 6 in the sample data).

It would look like this:

AcctNumSeqNumTranTypeTranAmtTranPeriod

1111020110101

1211020110201

131150

142-150

152-100

161150

Initially when reviewing the data, it appeared the TranPeriod should be used as a first match criteria.

However, since the system reverses transactions in this way, TranPeriod is important, but for a different reason.

If TranPeriod != 0, then it can match with a transaction where TranPeriod = 0 or the TranPeriods for both transactions are equal.

It seems to me that matches will only occur under these circumstances:

1. 2 opposite transactions with one following the other in SeqNum order (i.e, Original sample data SeqNums 8 & 9 and 10 & 11

2. Multiple reversals using matching inside out (i.e., Original sample data SeqNums 2 - 5)

3. In both cases, the TranPeriod needs to be considered as described above.

So if I modify the original sample data by adding a TranPeriod to SeqNums 3 & 4:

IF OBJECT_ID('tempdb..#TmpData','U') IS NOT NULL DROP TABLE #TmpData;

GO

SELECT *

INTO #TmpData

FROM (

SELECT 1,1,1,10,20110101 UNION ALL

SELECT 1,2,1,10,20110201 UNION ALL

SELECT 1,3,1,15,20110201 UNION ALL

SELECT 1,4,2,-15,20110301 UNION ALL

SELECT 1,5,2,-10,0 UNION ALL

SELECT 1,6,1,20,0 UNION ALL

SELECT 1,7,2,-20,0 UNION ALL

SELECT 1,8,2,-15,20110701 UNION ALL

SELECT 1,9,1,15,20110701 UNION ALL

SELECT 1,10,1,20,20110801 UNION ALL

SELECT 1,11,2,-20,20110801 UNION ALL

SELECT 1,12,2,-15,20110701

) T (AcctNum, SeqNum, TranType, TranAmt, TranPeriod);

SeqNums 2 - 5 no longer match. Since the dates on the inside pair (SeqNum 3 & 4) both have a TranPeriod and are not equal, they do not match. Therefore, the outside pair would no longer be considered a match. Once the match breaks, anything outside of the pair that does not match would not be a match.

The fun just never stops.:crazy:

GSquared SSC Guru Points: 260824 More actions · Answer 6

Judging by the business rule, one cancellation matches up to one and only one regular transaction, and a regular transaction can have zero or one cancellations, but not more than that, right?

In the TranPeriod, 0 essentially is defined by "equal to any one other TranPeriod", like a wildcard, right?

If those are both true statements, you'll need to process this record-by-record, or you'll end up with violations of the one-cancel/regular rule.

I'd do it by running a cursor on the regular transactions (type 1), and a "stack" of the cancellations. Check each transaction for a matching cancellation, one at a time, removing cancellations from the "stack" when they are matched up. So, a cursor for type 1 and a temp table for type 2.

I really don't see another way to do this that will guarantee correctness.

The data structure is essentially procedural, not relational. In a relational data structure, the order of rows doesn't matter to the definition of the row, but it does in this case because of the sequentiality of the cancellations. It's a procedural data structure, probably designed by a developer more familiar with procedural coding than with relational modeling.

From the viewpoint of someone used to working with stacks and other procedural concepts, this design makes total sense. So, I think the best way to move forward on it would be to keep to that paradigm and write code that conforms to procedural methodology.

Of course, even better (more best? [laughing at myself here]), would be to refactor it to work relationally, where a cancellation would actually be assigned to a specific regular transaction, possibly with something comparable to an ID-ParentID structure, or possibly in two separate tables, depending on business needs. But that might be outside the scope of your project and domain.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 7

I would be tempted to look at the code that enters the transactions and see if there is anything there that can be cribbed into your solution. But aside from that, Gus is probably correct about the best way to handle this.

EDIT: I'm especially interested in why the code would use 0 as a TranPeriod for your transactions. That might clear up a lot of things and give you a better sense on how to match things up.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

DougG SSChasing Mays Points: 612 More actions · Answer 8

Brandie Tarvin (1/25/2012)
I would be tempted to look at the code that enters the transactions and see if there is anything there that can be cribbed into your solution. But aside from that, Gus is probably correct about the best way to handle this.
EDIT: I'm especially interested in why the code would use 0 as a TranPeriod for your transactions. That might clear up a lot of things and give you a better sense on how to match things up.

Unfortunately, the code that enters the transactions is out of my control and not likely to change in the near future.

So, since it appears I must use a RBAR solution, here is what I have come up with:

IF OBJECT_ID('tempdb..#MatchTable','U') IS NOT NULL DROP TABLE #MatchTable;

GO

CREATE TABLE #MatchTable(

AcctNumdecimal(10, 0) NULL,

TranDatedecimal(8, 0) NULL,

TranSeqdecimal(4, 0) NULL,

Amountdecimal(11, 2) NULL,

PeriodDatedecimal(8, 0) NULL,

MatchSeqdecimal(4, 0) NULL,

TranIDint IDENTITY(1,1) NOT NULL

)

CREATE CLUSTERED INDEX [IX_MatchTable] ON #MatchTable

(AcctNum ASC,

TranDate ASC,

TranSeq ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

INSERT INTO #MatchTable

SELECT AcctNum, TranDate, TranSeq, Amount, PeriodDate, MatchSeq

FROM (

SELECT 6 AcctNum, 20090121 TranDate, 235 TranSeq, 2200.00 Amount, 20090101 PeriodDate, 0 MatchSeq UNION ALL

SELECT 6 AcctNum, 20090121 TranDate, 236 TranSeq, 2200.00 Amount, 20090101 PeriodDate, 0 MatchSeq UNION ALL

SELECT 6 AcctNum, 20090121 TranDate, 237 TranSeq, -2200.00 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 6 AcctNum, 20090121 TranDate, 238 TranSeq, -2200.00 Amount, 20090101 PeriodDate, 0 MatchSeq UNION ALL

SELECT 6 AcctNum, 20090121 TranDate, 239 TranSeq, 2200.00 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 6 AcctNum, 20090121 TranDate, 240 TranSeq, -2200.00 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 8 AcctNum, 20080131 TranDate, 482 TranSeq, -461.09 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 8 AcctNum, 20080131 TranDate, 487 TranSeq, 461.09 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 8 AcctNum, 20080303 TranDate, 507 TranSeq, -473.53 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 8 AcctNum, 20080303 TranDate, 512 TranSeq, 473.53 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 8 AcctNum, 20081024 TranDate, 552 TranSeq, -523.29 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 8 AcctNum, 20081024 TranDate, 556 TranSeq, 523.29 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 8 AcctNum, 20091105 TranDate, 644 TranSeq, -126.91 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 8 AcctNum, 20091105 TranDate, 647 TranSeq, 126.91 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20091016 TranDate, 49 TranSeq, -100.85 Amount, 20091001 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20091116 TranDate, 54 TranSeq, -100.85 Amount, 20091101 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20091216 TranDate, 60 TranSeq, -100.85 Amount, 20091201 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20100118 TranDate, 66 TranSeq, -100.85 Amount, 20100101 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20100216 TranDate, 70 TranSeq, -100.85 Amount, 20100201 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20100316 TranDate, 74 TranSeq, -100.85 Amount, 20100301 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20100331 TranDate, 84 TranSeq, -1109.35 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20100331 TranDate, 90 TranSeq, 1109.35 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20100421 TranDate, 106 TranSeq, -1008.50 Amount, 20080901 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20100421 TranDate, 107 TranSeq, -100.85 Amount, 20080901 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20100421 TranDate, 118 TranSeq, 1008.50 Amount, 20080901 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20100421 TranDate, 119 TranSeq, -1008.50 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20110614 TranDate, 113 TranSeq, 100.85 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 14 AcctNum, 20110614 TranDate, 114 TranSeq, 1008.50 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20081216 TranDate, 24 TranSeq, -29.58 Amount, 20081201 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20090116 TranDate, 36 TranSeq, -29.58 Amount, 20090101 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20100217 TranDate, 95 TranSeq, -381.48 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20100217 TranDate, 100 TranSeq, 381.48 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20100217 TranDate, 107 TranSeq, -381.48 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20100217 TranDate, 112 TranSeq, 381.48 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20100217 TranDate, 121 TranSeq, -381.48 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20100217 TranDate, 126 TranSeq, 381.48 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20110729 TranDate, 172 TranSeq, -29.58 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20110729 TranDate, 176 TranSeq, 29.58 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20110802 TranDate, 187 TranSeq, 408.94 Amount, 20110701 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20110802 TranDate, 188 TranSeq, -408.94 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20110802 TranDate, 189 TranSeq, 408.94 Amount, 20110701 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20110802 TranDate, 190 TranSeq, -408.94 Amount, 20110701 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20110803 TranDate, 215 TranSeq, 408.94 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 16 AcctNum, 20110803 TranDate, 216 TranSeq, -408.94 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110307 TranDate, 116 TranSeq, -15.00 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110419 TranDate, 122 TranSeq, 0.97 Amount, 20110301 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110419 TranDate, 123 TranSeq, -0.97 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110419 TranDate, 124 TranSeq, -15.97 Amount, 20110401 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110516 TranDate, 95 TranSeq, -0.97 Amount, 20110401 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110520 TranDate, 129 TranSeq, -0.97 Amount, 20110401 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110520 TranDate, 130 TranSeq, -15.00 Amount, 20110401 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110520 TranDate, 131 TranSeq, 15.00 Amount, 20110401 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110520 TranDate, 132 TranSeq, -15.00 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110520 TranDate, 159 TranSeq, -15.97 Amount, 20110401 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110520 TranDate, 160 TranSeq, -15.00 Amount, 20110401 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110523 TranDate, 103 TranSeq, 0.97 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110606 TranDate, 146 TranSeq, 15.00 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110606 TranDate, 147 TranSeq, 0.97 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110606 TranDate, 152 TranSeq, 15.97 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110606 TranDate, 163 TranSeq, 15.00 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 17 AcctNum, 20110606 TranDate, 164 TranSeq, 15.97 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20080322 TranDate, 361 TranSeq, -8.48 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 621 TranSeq, -10.61 Amount, 20110901 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 622 TranSeq, -10.61 Amount, 20110801 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 623 TranSeq, -10.61 Amount, 20110701 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 624 TranSeq, -10.61 Amount, 20110601 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 625 TranSeq, -10.61 Amount, 20110501 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 626 TranSeq, -10.61 Amount, 20110401 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 627 TranSeq, -10.61 Amount, 20110301 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 628 TranSeq, -10.61 Amount, 20110201 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 629 TranSeq, -9.75 Amount, 20110101 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 642 TranSeq, 9.75 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 643 TranSeq, 10.61 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 644 TranSeq, 10.61 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 645 TranSeq, 10.61 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 646 TranSeq, 10.61 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 647 TranSeq, 10.61 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 648 TranSeq, 10.61 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 649 TranSeq, 10.61 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 19 AcctNum, 20110926 TranDate, 650 TranSeq, 10.61 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20091015 TranDate, 50 TranSeq, -33.86 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20091116 TranDate, 55 TranSeq, -33.86 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20091218 TranDate, 64 TranSeq, -33.86 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20100615 TranDate, 93 TranSeq, -33.86 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101031 TranDate, 118 TranSeq, -33.86 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101223 TranDate, 133 TranSeq, -203.16 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101223 TranDate, 139 TranSeq, 203.16 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 145 TranSeq, 169.30 Amount, 20100801 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 146 TranSeq, -169.30 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 147 TranSeq, -33.86 Amount, 20101201 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 148 TranSeq, -33.86 Amount, 20101101 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 149 TranSeq, -33.86 Amount, 20101001 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 150 TranSeq, -33.86 Amount, 20100901 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 151 TranSeq, -33.86 Amount, 20100801 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 152 TranSeq, -33.86 Amount, 20090701 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 153 TranSeq, 33.86 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 154 TranSeq, 33.86 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 155 TranSeq, 33.86 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 156 TranSeq, 33.86 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 157 TranSeq, 33.86 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20101227 TranDate, 158 TranSeq, 33.86 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 22 AcctNum, 20111215 TranDate, 262 TranSeq, -14.41 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100324 TranDate, 112 TranSeq, -321.57 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100324 TranDate, 118 TranSeq, 321.57 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 172 TranSeq, 440.93 Amount, 20090901 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 173 TranSeq, 440.93 Amount, 20090901 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 174 TranSeq, -440.93 Amount, 20090901 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 175 TranSeq, -440.00 Amount, 20090901 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 176 TranSeq, -62.99 Amount, 20100501 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 177 TranSeq, -62.99 Amount, 20100401 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 178 TranSeq, -62.99 Amount, 20100301 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 179 TranSeq, -62.99 Amount, 20100201 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 180 TranSeq, -62.99 Amount, 20100101 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 181 TranSeq, -62.99 Amount, 20091201 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 182 TranSeq, -22.06 Amount, 20091101 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 183 TranSeq, -40.93 Amount, 20091101 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 184 TranSeq, -0.06 Amount, 20090901 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100614 TranDate, 185 TranSeq, -7.49 Amount, 20090901 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 197 TranSeq, 7.49 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 198 TranSeq, 0.06 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 199 TranSeq, 40.93 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 200 TranSeq, 22.06 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 201 TranSeq, 62.99 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 202 TranSeq, 62.99 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 203 TranSeq, 62.99 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 204 TranSeq, 62.99 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 205 TranSeq, 62.99 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 206 TranSeq, 62.99 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 207 TranSeq, 440.00 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 208 TranSeq, 440.93 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 209 TranSeq, -440.93 Amount, 0 PeriodDate, 0 MatchSeq UNION ALL

SELECT 30 AcctNum, 20100728 TranDate, 210 TranSeq, -440.93 Amount, 0 PeriodDate, 0 MatchSeq) A

ORDER BY AcctNum, TranDate, TranSeq

DECLARE

@AcctNumDECIMAL(10,0),

@TranIDINT,

@TranDateDECIMAL(8,0),

@TranSeqDECIMAL(4,0),

@AmountDECIMAL(11,2),

@PeriodDateDECIMAL(8,0),

@MatchSeqDECIMAL(4,0),

@MatchIDINT,

@ISMatchBIT

DECLARE C_MatchTable CURSOR

FOR SELECT AcctNum, TranID, TranDate, TranSeq, Amount, PeriodDate, MatchSeq

FROM #MatchTable

WHERE PeriodDate > 0

ORDER BY TranID

FOR UPDATE OF MatchSeq

OPEN C_MatchTable

FETCH NEXT FROM C_MatchTable

INTO @AcctNum, @TranID, @TranDate, @TranSeq, @Amount, @PeriodDate, @MatchSeq

WHILE @@FETCH_STATUS = 0

BEGIN

IF ISNULL(@MatchSeq,0) = 0

BEGIN

SET @ISMatch = 0

SELECT TOP 1 @ISMatch = CASE WHEN Amount + @Amount = 0 AND PeriodDate = @PeriodDate

THEN 1

ELSE 0

END,

@MatchID = TranID,

@MatchSeq = TranSeq

FROM #MatchTable

WHERE AcctNum = @AcctNum

AND TranID < @TranID

AND MatchSeq = 0

ORDER BY TranID DESC

IF @ISMatch = 1

BEGIN

UPDATE #MatchTable

SET MatchSeq = @MatchSeq

WHERE CURRENT OF C_MatchTable

UPDATE #MatchTable

SET MatchSeq = @TranSeq

WHERE TranID = @MatchID

END

FETCH NEXT FROM C_MatchTable

INTO @AcctNum, @TranID, @TranDate, @TranSeq, @Amount, @PeriodDate, @MatchSeq

END

CLOSE C_MatchTable

DEALLOCATE C_MatchTable

DECLARE C_MatchTable CURSOR

FOR SELECT AcctNum, TranID, TranDate, TranSeq, Amount, PeriodDate, MatchSeq

FROM #MatchTable

WHERE MatchSeq = 0

ORDER BY TranID

FOR UPDATE OF MatchSeq

OPEN C_MatchTable

FETCH NEXT FROM C_MatchTable

INTO @AcctNum, @TranID, @TranDate, @TranSeq, @Amount, @PeriodDate, @MatchSeq

WHILE @@FETCH_STATUS = 0

BEGIN

IF ISNULL(@MatchSeq,0) = 0

BEGIN

SET @ISMatch = 0

SELECT TOP 1 @ISMatch = CASE WHEN Amount + @Amount = 0 AND PeriodDate = CASE WHEN PeriodDate > 0 AND @PeriodDate > 0 THEN @PeriodDate ELSE PeriodDate END

THEN 1

ELSE 0

END,

@MatchID = TranID,

@MatchSeq = TranSeq

FROM #MatchTable

WHERE AcctNum = @AcctNum

AND TranID < @TranID

AND MatchSeq = 0

ORDER BY TranID DESC

IF @ISMatch = 1

BEGIN

UPDATE #MatchTable

SET MatchSeq = @MatchSeq

WHERE CURRENT OF C_MatchTable

UPDATE #MatchTable

SET MatchSeq = @TranSeq

WHERE TranID = @MatchID

END

FETCH NEXT FROM C_MatchTable

INTO @AcctNum, @TranID, @TranDate, @TranSeq, @Amount, @PeriodDate, @MatchSeq

END

CLOSE C_MatchTable

DEALLOCATE C_MatchTable

SELECT * FROM #MatchTable

This process works, but it runs for 6 hours across 440K records.

The first loop through looks at data where the PeriodDate > 0.

The adjoining records must match on the PeriodDate and adding the amounts together must = 0

This is uncommon in the entire population, but necessary. This loop only took 17 seconds. (I think can live with that :laugh:)

The sample data is fairly representative of the entire data population.

In the sample, loop one resulted in 3 matches.

The second loop looks at all remaining unmatched transactions.

When adding the adjoining record amounts, they must = 0.

This loop is only concerned with PeriodDate when both records have a PeriodDate > 0.

In this case, they must match. This process took 6 hours (and 9 seconds :hehe:).

Can this process be improved upon, to reduce the time?

Thanks so much.