First Occurrence for Each Client Phone Number

Question

First Occurrence for Each Client Phone Number

Allan1

SSC-Addicted

Points: 446
More actions
September 1, 2003 at 4:45 am

#159592

Hello -
I'm trying to find the first occurrence of a Phone Number (descending sort by Active Date) FOR EACH Client Number (Client_StaffID).
The table ([t_PhoneMail])is essentially as follows:
[Client_StaffID] [int] NOT NULL ,
[PhoneMailID] [int] IDENTITY (1, 1) NOT NULL ,
[PhoneNumber_Email] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Extension] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhoneTypeID] [int] NULL ,
[ActiveDate] [datetime] NULL, ...
Client_StaffID is a foreign key to the Client Table. There can be multiple phone numbers for each client, which can change over time (New numbers added or old ones deactivated). I want the most recent phone number (within each Phone Type) for Each Client_StaffID.
Anybody have a quick and easy solution (preferably a View or an SP). Any help is appreciated (need to finish up ASP.NET/SQL Server 2000 project this week).
Thanks, Allan.

Viewing 4 posts - 1 through 3 (of 3 total)

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

NPeeters SSChampion Points: 12541 More actions · Answer 1

Try this



SELECT PM.Client_StaffId,

       PM.PhoneNumber_Email,

       PM.Extension,

       PM.PhoneTypeId

FROM t_PhoneMail PM

WHERE PM.PhoneMailID =

    (SELECT TOP 1 TPM.PhoneMailId

           WHERE TPM.Client_StaffID = PM.ClientStaffID

             AND TPM.PhoneTypeID = PM.PhoneTypeID

     ORDER BY ActiveDate ASC)

Might need some work on the ORDER BY clause.

Edited by - NPeeters on 09/01/2003 06:13:29 AM

Allan1 SSC-Addicted Points: 446 More actions · Answer 2

Thanks. Here's the final version that works for me:

SELECT TOP 100 PERCENT Client_StaffID, PhoneMailID, PhoneNumber_Email, Extension, PhoneTypeID

FROM dbo.t_PhoneMail PM

WHERE (PhoneMailID =

(SELECT TOP 1 TPM.PhoneMailId

FROM dbo.t_PhoneMail TPM

WHERE TPM.Client_StaffID = PM.Client_StaffID AND TPM.PhoneTypeID = PM.PhoneTypeID

ORDER BY ActiveDate DESC)) AND (PhoneTypeID = 1)

ORDER BY Client_StaffID

Note that PhoneTypeID = 1 means Home Phone, 2 means Mobile Phone, 3 means Work Phone, 4 means Email Address, and 5 means FAX Number. (For those of you who need to know :-)).

Thanks again, Allan.

NPeeters SSChampion Points: 12541 More actions · Answer 3

Allan, great that I got you off in the right direction. I can see my (stupid) error now...