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

Master-Detail Searching, best approach?

Hello, I'm relatively new to using full-text search. I'm working on a =
document management program.
Basically my data it stored in two tables, [Documents], and =
[DocDetails], with a one-to-many relationship. [Documents] contains a =
record for each document stored, and [DocDetails] has many records =
concerning a document, each with an associated text field.
I'm really after the ability to search on a query '"jane" and "doe"', =
where I would be able to pull from [Documents] all documents that have =
matching records in [DocDetails]. My problem comes in how to query =
[DocDetails], some records would have "jane" in them, and some records =
would have "doe" in them, never would you see one record in [DocDetails] =
having both terms in the same text field. (I think this kinda negates =
using a strategy where the query has an "and" or "or" in it.
Maybe there is a way around this, I just haven't thought of. Right now, =
the only thing I can this is to split up my queries into only one word, =
and join the results.
If someone could give me a nudge in the right direction I'd appreciate =
it!
Thanks!,
--Michael
Raterus,
The below code was posted recently in regards to a "Parent - Child"
relationship, and should also work with your "Master - Detail" searching.
Tables:
contact(contact_id, name)
role(role_id, description)
contact_role (contact_id, role_id)
How can I find all contact where the search string is in the contact name
and or roles description? This is the quick and simplest way. You can
rewrite this query using outer-joins. The code is not tested. So expect some
syntax errors.
select * from contact where contactid in
(select contactid from contact_role where contactid in (select key from
containstable(contact,name,'search string') or
roleid in (select key from containstable(role,description,'search
string'))
You should be able to alter the above code to fit your tables. If you have
an alternative approach or additional questions, please post them here..
Regards,
John
"Raterus" <raterus@.spam.org> wrote in message
news:OI#yuv1NEHA.556@.TK2MSFTNGP10.phx.gbl...
Hello, I'm relatively new to using full-text search. I'm working on a
document management program.
Basically my data it stored in two tables, [Documents], and [DocDetails],
with a one-to-many relationship. [Documents] contains a record for each
document stored, and [DocDetails] has many records concerning a document,
each with an associated text field.
I'm really after the ability to search on a query '"jane" and "doe"', where
I would be able to pull from [Documents] all documents that have matching
records in [DocDetails]. My problem comes in how to query [DocDetails],
some records would have "jane" in them, and some records would have "doe" in
them, never would you see one record in [DocDetails] having both terms in
the same text field. (I think this kinda negates using a strategy where the
query has an "and" or "or" in it.
Maybe there is a way around this, I just haven't thought of. Right now, the
only thing I can this is to split up my queries into only one word, and join
the results.
If someone could give me a nudge in the right direction I'd appreciate it!
Thanks!,
--Michael
|||Actually that was the first post I read before I posted, it helped =
initally. I've adapted it a little bit and have come up with this. =
It's a mess, I know it :-) It is working how I want it to though, the =
only problem I forsee, is how do I have different queries for a =
different number of search terms. Using this strategy I'll have to have =
a different query if they search for "jane doe john brown" rather than =
just "jane doe" This is all for an asp.net web application, so it =
wouldn't be that difficult for me to create my own query on the fly and =
send it over, but if there is a better way, I'd love to hear it!
select * from documents where docID in
(
select t1.docID
from (
select docID
from docdetails
where ddID in
(
select [key]
from containstable(docdetails,value,'"jane"')
)
) as t1 inner join=20
(
select docID
from docdetails
where ddID in
(
select [key]
from containstable(docdetails,value,'"doe"')
)
) as t2 on t1.docID =3D t2.docID
)
"John Kane" <jt-kane@.comcast.net> wrote in message =
news:%235axQM2NEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Raterus,
> The below code was posted recently in regards to a "Parent - Child"
> relationship, and should also work with your "Master - Detail" =
searching.
>=20
> Tables:
> contact(contact_id, name)
> role(role_id, description)
> contact_role (contact_id, role_id)
>=20
> How can I find all contact where the search string is in the contact =
name
> and or roles description? This is the quick and simplest way. You can
> rewrite this query using outer-joins. The code is not tested. So =
expect some
> syntax errors.
>=20
> select * from contact where contactid in
> (select contactid from contact_role where contactid in (select key =
from
> containstable(contact,name,'search string') or
> roleid in (select key from containstable(role,description,'search
> string'))
>=20
> You should be able to alter the above code to fit your tables. If you =
have
> an alternative approach or additional questions, please post them =
here..
>=20
> Regards,
> John
>=20
>=20
>=20
>=20
> "Raterus" <raterus@.spam.org> wrote in message
> news:OI#yuv1NEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hello, I'm relatively new to using full-text search. I'm working on a
> document management program.
>=20
> Basically my data it stored in two tables, [Documents], and =
[DocDetails],
> with a one-to-many relationship. [Documents] contains a record for =
each
> document stored, and [DocDetails] has many records concerning a =
document,
> each with an associated text field.
>=20
> I'm really after the ability to search on a query '"jane" and "doe"', =
where
> I would be able to pull from [Documents] all documents that have =
matching
> records in [DocDetails]. My problem comes in how to query =
[DocDetails],
> some records would have "jane" in them, and some records would have =
"doe" in
> them, never would you see one record in [DocDetails] having both terms =
in
> the same text field. (I think this kinda negates using a strategy =
where the
> query has an "and" or "or" in it.
>=20
> Maybe there is a way around this, I just haven't thought of. Right =
now, the
> only thing I can this is to split up my queries into only one word, =
and join
> the results.
>=20
> If someone could give me a nudge in the right direction I'd appreciate =
it!
>=20
> Thanks!,
> --Michael
>=20
>

Master/Detail table insertion.

Hi Experts,

I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain

Insertion in Master Table..................................... A

Insertion in Detail Table........................................B

Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.

So please can any one tell me a good approach for this.

This my solution using trigger and SET XACT_ABORT ::

create table A (a int)

go

create table B (a int)

create trigger t_A on A

after insert

as

set xact_abort off

if @.@.error <>0

begin

print 'error in master'

rollback tran

else

begin

commit tran

insert into b

select a from inserted

-- values ('gigi')

end

if you run

insert into a values (1) the value 1 is inserted in A and B

for testing how is behavior on error of inserting in B table, unn comment "-- values ('gigi')" and comment " select a from inserted " and run the command " alter triger t_A ...." ()modifying trigger)

if you run insert into a values (2) the value 2 will be insert in A table and inserting will not be possible in B table because is an error .

So you have to modify the operation of inserting in B

" insert into b

select a from inserted

-- values ('gigi')

"

as your business logic request.

|||

Thanks for the reply but i dont want to use trigers because it can affect the performance.

Thanks for giving time.

|||

Did you calculate how is affected the performance ? Obviously the trigger have to be avoided but in some cases it is usually.

|||You'll probably want to encapsulate the inserts within a transaction with logic to perform a ROLLBACK if anything goes wrong or a COMMIT when all of the work is complete.

There is a vast amount of information in Books Online on transaction management.

Put simply your logic might look something like this.

BEGIN TRANSACTION

INSERT INTO A
INSERT INTO B
INSERT INTO B
INSERT INTO B

IF NO ERROR THEN

COMMIT ELSE
ROLLBACK

|||Thanks Sir,

Please can u explain that how can i send array because according to your solution i will have to use array for my detail information for example if my master table is ORDER and my detail table is ORDER_DETAIL then the list of products are to be send in array which i dont know how will i do.

If you can provide a simple code i will be thankfull to you.

Thanks.

Master/Detail table insertion.

Hi Experts,

I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain

Insertion in Master Table..................................... A

Insertion in Detail Table........................................B

Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.

So please can any one tell me a good approach for this.

This my solution using trigger and SET XACT_ABORT ::

create table A (a int)

go

create table B (a int)

create trigger t_A on A

after insert

as

set xact_abort off

if @.@.error <>0

begin

print 'error in master'

rollback tran

else

begin

commit tran

insert into b

select a from inserted

-- values ('gigi')

end

if you run

insert into a values (1) the value 1 is inserted in A and B

for testing how is behavior on error of inserting in B table, unn comment "-- values ('gigi')" and comment " select a from inserted " and run the command " alter triger t_A ...." ()modifying trigger)

if you run insert into a values (2) the value 2 will be insert in A table and inserting will not be possible in B table because is an error .

So you have to modify the operation of inserting in B

" insert into b

select a from inserted

-- values ('gigi')

"

as your business logic request.

|||

Thanks for the reply but i dont want to use trigers because it can affect the performance.

Thanks for giving time.

|||

Did you calculate how is affected the performance ? Obviously the trigger have to be avoided but in some cases it is usually.

|||You'll probably want to encapsulate the inserts within a transaction with logic to perform a ROLLBACK if anything goes wrong or a COMMIT when all of the work is complete.

There is a vast amount of information in Books Online on transaction management.

Put simply your logic might look something like this.

BEGIN TRANSACTION

INSERT INTO A
INSERT INTO B
INSERT INTO B
INSERT INTO B

IF NO ERROR THEN

COMMIT ELSE
ROLLBACK

|||Thanks Sir,

Please can u explain that how can i send array because according to your solution i will have to use array for my detail information for example if my master table is ORDER and my detail table is ORDER_DETAIL then the list of products are to be send in array which i dont know how will i do.

If you can provide a simple code i will be thankfull to you.

Thanks.