May 1, 2006 at 11:34 am
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.
May 1, 2006 at 12:19 pm
Something like this , Check for syntax
FROM A JOIN B ON A.PhoneNumber = B.PhoneNumber
AND A.Date=B.Date AND ((A.Minutes - B.Minutes) NOT BETWEEN (-0.5 AND 0.5))
AND (DateDiff(mm,A.CallTime,B.CallTime) NOT Between (-1 AND 1))
Amit Lohia
May 2, 2006 at 8:28 am
I believe Amit's logic is reversed. This works:
select A.PhoneNumber, A.Call_Time, B.Call_Time, A.Minutes, B.Minutes
from Table1 A inner join Table2 B
ON A.PhoneNumber = B.PhoneNumber
where
(DateDiff(ss,A.Call_Time,B.Call_Time) Between -60 AND 60)
and
((A.Minutes - B.Minutes) BETWEEN -0.5 AND 0.5)
Table1:
| PhoneNumber | Call_Time | Minutes |
| 1111111111 | 8:30:15 AM | 1.5 |
| 1111111111 | 8:45:23 AM | 2.5 |
| 1111111111 | 9:15:30 AM | 3.5 |
| 1111111111 | 9:30:59 AM | 4.01 |
Table2: | ||
| PhoneNumber | Call_Time | Minutes |
| 1111111111 | 8:30:15 AM | 1.99 |
| 1111111111 | 8:45:23 AM | 3.01 |
| 1111111111 | 9:16:31 AM | 3.5 |
| 1111111111 | 9:30:00 AM | 4.5 |
The query above will only select the first and fourth rows, which is what we want.
May 2, 2006 at 10:48 am
If it does not match, I'd like to display that seperately.
I think he wants the one which does not match or may be both.
DateDiff(ss,A.Call_Time,B.Call_Time) is better than DateDiff(mm,A.Call_Time,B.Call_Time)
Amit Lohia
May 2, 2006 at 11:37 am
Here's what I wound up doing:
(find unmatched entries)
FROM A
WHERE (NOT EXISTS
(SELECT *
FROM B
WHERE A.Phone_Number = dbo.ccm.Phone_Number AND A.call_date = B.call_date AND
A.Minutes BETWEEN dbo.ccm.minutes - 0.5 AND B.minutes + 0.5 AND abs(datediff(minute,
A.Call_Time, B.Call_Time)) <= 1))
(find matched entries)
FROM A INNER JOIN
B ON A.PHONE_NUMBER = B.phone_number AND A.CALL_DATE = B.CALL_DATE
WHERE (A.MINUTES BETWEEN B.MINUTES - 0.5 AND B.MINUTES + 0.5) AND (ABS(DATEDIFF(minute, A.CALL_TIME,
B.CALL_TIME)) <= 1)
Thanks for your help, guys!
May 2, 2006 at 11:41 am
Not exists will be slow and use DATEDIFF(seconds instead of DATEDIFF(minute
Amit Lohia
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply