Matching Transactions and Duplicates

Can anyone suggest a good approach to this problem?
I need to match transactions from two tables on several columns. When
I have a match, I need to set a match flag and a match number in each
table. There can be duplicate transactions in either table. If I have
two transactions in one table that match three in the other, I want to
set the match flag and match number on two transactions in each table
and leave the third one blank.
Results:
table 1
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
5 5 Y 3
5 5
table 2
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
1 2
5 5 Y 3>> Can anyone suggest a good approach to this problem?
Please post your table structures & expected results along with clear
explanation of what you are trying to do. For details, refer to :
www.aspfaq.com/5006
Anith|||I have two temp tables loaded with the transactions I want to match
with the other table. They need to match on mid, tran_date, amount,
and card. When there's a match, I want to set the match_flag to 'AM'
and set the match_number to a unique number given to each matching
transaction. It's possible that there can be duplicate transactions,
as in the samples below, where there isn't a corresponding match in the
other table. If there are three in one table and two in the other that
match, I want to set the match_flag and match_number on two from each
table and leave the third from one table null to match on a possible
future load. I'll be matching on several thousands of transactions.
Let me know if you need more information. Thanks.
create table #setl (
mid char(6) not null,
load_number int not null,
detail_number int not null,
batch_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
create table #debit (
mid char(6) not null,
load_number int not null,
detail_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
desired results:
#setl
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2
2005-09-30 .01 4444 null null
#debit
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2|||First all, in databases, duplicates nullify logic. In other words, without a
column or set of column to uniquely identify a row in a table, it is
impossible to logically manipulate the data in those tables.
Given the data in your tables, it is impossible to tell if the duplicate
rows represent actually duplicated transactions or simply erroneous entries.
So rather than trying to work with meaningless data, you should consider
eliminating the duplicates in the first place. For starters refer to:
http://support.microsoft.com/kb/139444/en-us
If you are looking for a short term workaround to please your boss, write up
a cursor to loop through the rows and assign the match_flag and match_number
values. However, such a solution will do no good since you will still be
left with redundant data with no keys and constraints.
Anith