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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-30 : 08:37:06
|
| asif writes "sql server 2000 service pack latestThe desired result of queryi 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 queryTwo tables named gateway_unmatched and P_calltel are ineer joinedalias for gateway_unmatched is ualias for p_calltel is ppcli is matching uclipdest is matching udesttolereance is working means after adding 5:30 the result is shownResults are apperd as p table field then u table filed then p table field then u table field and so onthe problemeverything 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 implmenteddesired resulti 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 ui check that there is only one record in the table but the query presents more then one record which is wrongselect 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_newdtimefrom gateway_unmatched u inner join p_calltel pon ((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 3082070163 3720 447546 447546 447966 447966 30815 30815 70164 3720 447546 447546 447966 447966 30815 30815p_newdtime u_newdtime01:02:56 01:03:3601:07:57 01:06:2001:03:45 01:06:20 " |
|
|
dsdeming
479 Posts |
Posted - 2003-10-31 : 08:06:14
|
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 |
 |
|
|
|
|
|
join