Post reply

First Occurrence for Each Client Phone Number

  • 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.

  • September 1, 2003 at 6:04 am

    #472095

    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

  • September 1, 2003 at 12:24 pm

    #472129

    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.

  • September 2, 2003 at 12:58 am

    #472155

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

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

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