help me out here please!

Question

help me out here please!

uzwa ul haque

SSChasing Mays

Points: 607
More actions
October 17, 2005 at 1:08 am

#188123

i have a master table and a detail table. they r in a 1 to many relationship, they both have a phone number field in common.
my task is to take phone number from master and take out the last occurance of that phone number in detail table(since there r more than 1 rec in detail table against 1 telephone number from master).
problem is i have 239664 records in in master and half of them in master, can u guyz help me out here with some optimized query or an sp which could get me results faster

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Could you please post the DDL of your tables and some example data? It'll make things a lot easier for us to come up with a solution.

What do you mean by last occurrence of the phone number? Data in SQL tables has no defined sort order. If you want the latest, you'll need a date somewhere or something like that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

uzwa ul haque SSChasing Mays Points: 607 More actions · Answer 2

Ok here is the structure

Master Table:

Detail Table:

Id|Day|Result|Campaign|PhoneNumber

Dummy Data For Master:

1|44786632|Dummy|Stree#1|CA|2253|45866347

2|45689632|Dummy2|Stree#2|CA|2253|45866347

Dummy Data for Detail Table:

1|Tue, Sep 13, 2005|No Answer|LGS Outbound|44786632

2|Wed, Sep 13, 2005|No Answer|LGS Outbound|44786632

3|Wed, Sep 15, 2005|No Answer|LGS Outbound|44786632

AS u can see above there can me multiple occurance of a single phone number from MAster in the detail table:44786632 exists 3 times in Detail and only 1 time in master, what i want is the last occurance of the record against this number which is the marked as Italic in Detail Table dummy data.

there r 3 records in detail againsta this number and i want the last one(order by Id ofcourse)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

This should get you what you want.

SELECT MasterTable.Name, DetailTable.Id, DetailTable.Day, DetailTable.Result, DetailTable.Campaign, DetailTable.PhoneNumber FROM DetailTable INNER JOIN

(SELECT MAX(ID) AS LatestID, PhoneNumber

FROM DetailTable

GROUP BY PhoneNumber) LatestEntry ON DetailTable.ID=LatestEntry.LatestID AND DetailTable.PhoneNumber=LatestEntry.PhoneNumber

INNER JOIN MasterTable ON MasterTable.PhoneNumber=DetailTable.PhoneNumber

It should be reasonable fast if you have appropriate indexs on Detail table. (Off the top of my head, I'd suggest a Nonclustered on PhoneNumber and ID, or just on PhoneNumber if ID is the clustered index)

HTH

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

uzwa ul haque SSChasing Mays Points: 607 More actions · Answer 4

thankx for the tip its fetching data but i have to scrutinize it to check whether i'm geting right data or i need more help from u guyz

Steve Fine Ten Centuries Points: 1164 More actions · Answer 5

Suggestion: Put IdentityKey in detail table (D). Put LastCallID field in Master (M). Have Detail OnInsert Trigger update M.LastCallID with D.IdentityKey. If you are always looking for the last call from all phone numbers, establish a view with Master M Inner Join Detail D On M.LastCallID = D.IdentityKey