Post reply

search rows having same data in different table

  • October 7, 2007 at 2:06 am

    #175792

    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

    #739852

    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

    #739879

    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.

    jwilliamsoh@gmail.com

    <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