Compare two tables and match entries, stored proc

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

SQL Server Development (2000)

Compare two tables and match entries, stored proc

Author

Topic

Nystix
Starting Member

4 Posts

Posted - 2006-05-01 : 13:17:48

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.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-05-01 : 15:26:03

Move your table links to the JOIN clause, where they belong, and then try this logic for your fuzzy-matching:

FROM	Table1
	inner join Table2
		on Table1.PhoneNumber = Table2.PhoneNumber
		and Table1.Date = Table2.Date
WHERE	Table1.Minutes between Table2.minutes -0.5 and Table2.minutes + 0.5
	and abs(datediff(minute, Table1.Call_Time, Table2.Call_Time)) <= 1

Nystix
Starting Member

4 Posts

Posted - 2006-05-01 : 16:05:48

That worked PERFECTLY. Thanks so much!

Now, how do I pull those records NOT found in that query? Can I do that within here somehow as well?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-05-01 : 16:41:43

Two methods:

select	Table1.*
FROM	Table1
	left outer join Table2
		on Table1.PhoneNumber = Table2.PhoneNumber
		and Table1.Date = Table2.Date
		and Table1.Minutes between Table2.minutes -0.5 and Table2.minutes + 0.5
		and abs(datediff(minute, Table1.Call_Time, Table2.Call_Time)) <= 1
WHERE	Table2.PhoneNumber is null

--or--

select	Table1.*
FROM	Table1
WHERE	NOT EXISTS
	(Select	*
	from	Table2
	where	Table1.PhoneNumber = Table2.PhoneNumber
		and Table1.Date = Table2.Date
		and Table1.Minutes between Table2.minutes -0.5 and Table2.minutes + 0.5
		and abs(datediff(minute, Table1.Call_Time, Table2.Call_Time)) <= 1)

Nystix
Starting Member

4 Posts

Posted - 2006-05-02 : 10:15:44

You're a life saver. Thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources