Match Canceling Records

Question

Match Canceling Records

DougG

SSChasing Mays

Points: 612
More actions
January 23, 2012 at 11:13 am

#254044

I have 2 types of transactions that possibly cancel each other out.
It is possible to have transactions of either type without a matching cancel transaction. (there will be more transaction type 1s that do not have a matching transaction type 2)
There may or may not be a period date (TranPeriod). When there is a matching period date for both transaction types and the amounts cancel, they should be considered a match.
When there are multiple transaction type 1s followed by multiple transaction type 2s, the matches will occur from the inside to the outside - see TranSeq 2- 5 for example.
TranSeq 9 - 12 would work the same as TranSeq 2- 5, except TranSeq 8 matches TranSeq 9 due to same PeriodDate and the amounts cancel.
What I need to do is either mark or delete transactions that match.
If I have not been clear, please let me know.
Here is some test data:
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,0 UNION ALL
SELECT 1,4,2,-15,0 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,0
) T (AcctNum, SeqNum, TranType, TranAmt, TranPeriod)
;
Thanks for your help.

Viewing 15 posts - 1 through 15 (of 22 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Good job of posting sample data, but the table definition is a bit unclear... everything is going to be stored as an integer.

So, based upon this sample data, what are the expected results?

(You will get better answers if you read and heed the first link in my signature block.)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

DougG SSChasing Mays Points: 612 More actions · Answer 2

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Part of what Wayne was saying is that in your sample data you create a temp table implicitly and every single column is an int. You said something about a date but you have either ints (that kind of look like dates) or 0's. Is everything really supposed to be ints or do you have some other datatypes?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DougG SSChasing Mays Points: 612 More actions · Answer 4

INT is fine for this example. When there is a date it is stored YYYYMMDD.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Well then help me understand your matching criteria...

There may or may not be a period date (TranPeriod). When there is a matching period date for both transaction types and the amounts cancel, they should be considered a match.

But then in your sample output you have SeqNum 2 & 5 being a match. SeqNum 2 has a TranPeriod of 20110201 and SeqNum 5 has 0.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DougG SSChasing Mays Points: 612 More actions · Answer 6

Right, so in priority order:

1. There may or may not be a period date (TranPeriod). When there is a matching period date for both transaction types and the amounts cancel, they should be considered a match.

2. When there are multiple transaction type 1s followed by multiple transaction type 2s, the matches will occur from the inside to the outside - see TranSeq 2- 5 for example.

If the transactions can be matched by TranPeriod, this takes precedence. If not, #2 kicks in. If they cannot be matched by #2, then they do not match.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Wow that is nasty.

I get what you mean about the transaction period thing but "inside out". That is pretty ambiguous. Do you look first for an earlier one and then a later one? Or do you find the one that is closest? How would you handle a tie? And of course if the transaction is unrelated how do you define it? This whole inside out thing is so odd ball. Not LIFO, not FIFO, but closest based number of attempted inserts (assuming it is an identity).

I can't even begin to come up with an idea that would not be horribly slow, incredibly inefficient and would likely get me kicked out of the anti-rbar coalition. 🙂

It is almost like you would have to start in the middle and work yourself out backwards. I can't begin to wrap my head around a business case where this makes any sense.

I am outta here for today but I will toss around my brain tonight.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DougG SSChasing Mays Points: 612 More actions · Answer 8

I get what you mean about the transaction period thing but "inside out". That is pretty ambiguous. Do you look first for an earlier one and then a later one? Or do you find the one that is closest? How would you handle a tie? And of course if the transaction is unrelated how do you define it? This whole inside out thing is so odd ball. Not LIFO, not FIFO, but closest based number of attempted inserts (assuming it is an identity).

For a little further clarification: TranSeq = the order the transactions have been entered for each AcctNum.

So AcctNum & TranSeq = identity

I cannot think of a way there would be a tie. The match would always occur on the transactions closest to each other.

So even if you had something like this:

TranSeqTranTypeTranAmtTranPeriod

11 100

22-100

32-100

TranSeq 1 and 2 would match. 3 would not match.

I believe the sample data shows all of the ways the transactions could be arranged, with one exception:

an inside out with multiple TranType 2s prior to multiple TranType 1s.

And yes I have beat my head against the wall more than a few times due to our transaction system.

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

I cannot think of a way there would be a tie.

What about something like this:

TranSeqTranTypeTranAmtTranPeriod

12-100

21100

32-100

Here is what I mean by a tie. Now which one would be the correct match? There are two TranType 2 that are "closest".

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DougG SSChasing Mays Points: 612 More actions · Answer 10

DougG

SSChasing Mays

Points: 612

January 24, 2012 at 7:33 am

#1437679

TranSeq 1 & 2 would match. 3 would not match.

DougG SSChasing Mays Points: 612 More actions · Answer 11

Is there something else I need to add?

Or am I just stuck with RBAR?

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

I can't come up with anything other than RBAR for this. I would think about doing this in an external app because I suspect you are going to have to loop through these more than once. Maybe somebody will come along with a better idea but you stumped me.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DougG SSChasing Mays Points: 612 More actions · Answer 13

Thanks for spending time on this.

Do I win a prize? 😀

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

DougG (1/25/2012)
Thanks for spending time on this.
Do I win a prize? 😀

LOL. If it was difficult to stump me perhaps. 😀 I can the group and see if anybody else has some ideas.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/