"Match" entries with margin of error in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

"Match" entries with margin of error in stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-04 : 08:43:10

N writes "I need some help creating a stored procedure that compares two tables, and "matches" the entries. If it does not match, I'd like to display that seperately.

This is how I'd like it to run:

WHERE Table1.PhoneNumber = Table2.PhoneNumber, Table1.Date=Table2.date AND

WHERE
Table1.Minutes = Table2.minutes (with a +/- 0.5 margin either way, on either field)

AND

Table1.Call_Time = Table2.Call_Time (with a +/- 1 minute margin either way, on either field)

Right now I have a UNION ALL running, which only matches exactly (so the two columns that I want to have a margin of error don't run correctly)

Any assistance is greatly appreciated."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-04 : 09:30:14

try this:


WHERE	Table1.PhoneNumber = Table2.PhoneNumber
and	Table1.Date = Table2.date 
AND	abs(Table1.Minutes - Table2.minutes) <= 0.5
AND	abs(datediff(minute, Table1.Call_Time, Table2.Call_Time)) <= 1

EDIT:
this assumes Minutes is some type of numeric datatype and call_time is datetime or smalldatetime. If that is not the case, post again with the table DDL (create table statements) and some sample values (in the form of insert statements)

Be One with the Optimizer
TG