October 7, 2007 at 2:06 am
Hi,
I have 4 tables :
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);
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.
October 7, 2007 at 12:09 pm
Answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90392
N 56°04'39.16"
E 12°55'05.25"
October 7, 2007 at 9:07 pm
Sounds like you work in the telecom space, as do I.
Feel free to contact me directly if you like. I would like to know a little more about the application of what you are doing here. There are many ways that you can accomplish this however some factors come into play such as;
Is this a real-time lookup as CDR's are streamed?
Is this for a report, a rating engine? It may make a difference where you actually do this lookup, perhaps embedding into existing processes somewhere is more logical.
In any event feel free to write me and I would be happy to do some discovery with you.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply