Duplicate search - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Duplicate search

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-10-07 : 04:03:28

Hi,

I have 4 tables :

[Code]
SAMPLE

Create Table #Request ( [requestid] int , [customername] Varchar(30) , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);

Create Table #Dup ([dupid] int , [cname] Varchar(30), [cityno] int);

Create Table #Call(Callid int,Calltype int,callDetailid int )

Create Table #CallDetail(callDetailId int,empid int)

Insert into #CallDetail VALUES(12123,1)
Insert into #CallDetail VALUES(53423,1)
Insert into #CallDetail VALUES(6532,1)
Insert into #CallDetail VALUES(82323,1)
Insert into #CallDetail VALUES(124235,1)

Insert Into #Call VALUES(111,1,12123)
Insert Into #Call VALUES(112,1,53423)
Insert Into #Call VALUES(114,1,6532)
Insert Into #Call VALUES(123,2,6532)
Insert Into #Call VALUES(134,1,124235)

Insert Into #request Values('324234','Jack','SA023',12,111,0);
Insert Into #request Values('223452','Tom','SA023',12,112,0);
Insert Into #request Values('456456','Bobby','SA024',12,114,0);
Insert Into #request Values('22322362','Guck','SA024',44,123,0);
Insert Into #request Values('22654392','Luck','SA023',12,134,0);

Insert Into #Dup VALUES (1, 'Bobby',12);
Insert Into #Dup VALUES (2, 'Harry',12);
Insert Into #Dup VALUES (3, 'Timothy',14);
Insert Into #Dup VALUES (4, 'Guck',44);
Insert Into #Dup VALUES (5, 'Angela',44);

[/CODE]

1. My stored proc will take the CallDetailID.

2. I have to find out the requests made on this calldetail.

3. After getting the request, i want to take the Customername, cityno of the request found and pass it to Dup table to search for any duplicates.

4. If found return those duplicates:

Eg:

I pass in CallDetailID as 6532.

There are 2 records in Call table for this i.e (114,123). From this i get the request rows

456456','Bobby','SA024',12,114,0
'22322362','Guck','SA024',44,123,0

After getting these 2 #Request rows, i have to pass the CustomerName and CityNo of these 2 request rows to the #DUP table to see if there is similar data present. If yes return the rows to the client.

OUTPUT should be

1, 'Bobby',12
4, 'Guck',44

#Dup can contain millions of rows. Please suggest how to do this. Please give t-sql statement.

thanks a ton.

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-07 : 17:47:11

Try this....

select s.* from
(
select c.*,r.customername,r.cityno
from #Call c
join #CallDetail cd
on c.calldetailid = cd.calldetailid
join #Request r
on r.callid = c.callid
where cd.calldetailid = '6532') as s
join #dup d
on s.customername = d.cname
and s.cityno = d.cityno

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-07 : 19:04:40

select * from
#CaLL a
inner join
#Request b
on a.CallID = b.CallID
where a.CallDetailID = 6532
and exists (Select * From #Dup aa where aa.CName = b.Customername and aa.CityNo = b.CityNo)

lols
Posting Yak Master

174 Posts

Posted - 2007-10-08 : 02:04:24

awesome!! thanks a lot to both of you. The logic runs well. Any idea to optimize this further. It takes around 3 seconds to execute against 400000 rows

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 02:08:33

"Any idea to optimize this further"

Index:

#CaLL.CallID
#Request.CallID
#Request.Customername,CityNo
#Dup.CName,CityNo

Kristen

lols
Posting Yak Master

174 Posts

Posted - 2007-10-08 : 03:10:04

ok..thanks a lot

ANy ideas how can i modify this query
select s.* from
(
select c.*,r.customername,r.cityno
from #Call c
join #CallDetail cd
on c.calldetailid = cd.calldetailid
join #Request r
on r.callid = c.callid
where cd.calldetailid = '6532') as s
join #dup d
on s.customername = d.cname
and s.cityno = d.cityno

to search for duplicates within the #request table itself. So instead of searching in #Dup table, i want to use the same query to search within #request table.

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 03:40:08

Why would you like to join in the CallDetail table? The column CallDetailID is already present in Call table.
And, as a continuation from this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90392
I suggest

SELECT		d.DupId,
		d.CName,
		d.CityNo
FROM		#Request AS r
INNER JOIN	#Call AS c ON c.CallID = r.CallID
INNER JOIN	#Dup AS d ON d.CName = r.CustomerName AND d.CityNo = r.CityNo
WHERE		c.CallDetailID = 6532

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources