SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 join
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/30/2003 :  08:37:06  Show Profile  Visit AskSQLTeam's Homepage
asif writes "sql server 2000 service pack latest

The desired result of query
i want all records which are found same in both table but only the filed of datetime where i am giving tolerance of 5 minutes 30 seconds means(any record which has the difference of 5:30 would be matched) but cartition product is not allowd be remember.

Too describe the query

Two tables named gateway_unmatched and P_calltel are ineer joined
alias for gateway_unmatched is u
alias for p_calltel is p
pcli is matching ucli
pdest is matching udest
tolereance is working means after adding 5:30 the result is shown
Results are apperd as p table field then u table filed then p table field then u table field and so on

the problem
everything is working fine besides the duplicate record and you can see in the result of record no 2 and 3 that it is duplicating because of the tolerance i implmented

desired result
i would like to elemenate that record which comes two time in the result please check the seq no and you will see the duplicaton in the table of u
i check that there is only one record in the table but the query presents more then one record which is wrong


select p.seqno pseq, u.seqno ueq,
p.newcli pcli, u.newcli ucli,
p.destination pdest, u.destination udest,
p.newdatetime p_newdtime, u.newdatetime u_newdtime

from gateway_unmatched u inner join p_calltel p

on ((p.newcli = u.newcli)
and (p.destination u.destination)
and p.newdatetime <= cast(u.newdatetime + cast('00:05:30' as datetime) as datetime)
and p.newdatetime >= cast(u.newdatetime + cast('00:05:30' as datetime) as datetime)

order by u.ans_time

pseq useq pcli ucli pdest udest pdt udt
23211 8184 427311 427311 966536 966536 30820 30820
70163 3720 447546 447546 447966 447966 30815 30815
70164 3720 447546 447546 447966 447966 30815 30815

p_newdtime u_newdtime
01:02:56 01:03:36
01:07:57 01:06:20
01:03:45 01:06:20 "

dsdeming
Constraint Violating Yak Guru

USA
479 Posts

Posted - 10/31/2003 :  08:06:14  Show Profile
If you'll provide CREATE scripts for the tables and some INSERT statements to populates them with sample data, you'll probably get the answer you need.

quote:
and p.newdatetime <= cast(u.newdatetime + cast('00:05:30' as datetime) as datetime)
and p.newdatetime >= cast(u.newdatetime + cast('00:05:30' as datetime) as datetime)



BTW, don't the two statements above actually simplify down to

p.newdatetime = cast(u.newdatetime + cast('00:05:30' as datetime) as datetime)?

Dennis
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000