SQL Help

  • DECLARE @CustMaster

    TABLE (

    CustID VARCHAR(10) NOT NULL,

    CustName VARCHAR(50) NULL,

    CustPhone VARCHAR(10) NULL

    );

    DECLARE @CustCalls

    TABLE (

    CustID VARCHAR(10) NOT NULL,

    ContactName VARCHAR(50) NULL,

    ContactPhone VARCHAR(10) NULL,

    CallDate DATETIME NULL

    );

    INSERT @CustMaster (CustID, CustName, CustPhone)

    VALUES ('1', 'Customer1', '12345345');

    INSERT @CustMaster (CustID, CustName, CustPhone)

    VALUES ('2', 'Customer2', '12345787');

    INSERT @CustMaster (CustID, CustName, CustPhone)

    VALUES ('3', 'Customer3', '34234344');

    INSERT @CustCalls (CustID, ContactName, ContactPhone, CallDate)

    VALUES ('1', 'Name1', '23423434','2010-01-01');

    INSERT @CustCalls (CustID, ContactName, ContactPhone, CallDate)

    VALUES ('1', 'Name1', '45454545','2010-02-02');

    INSERT @CustCalls (CustID, ContactName, ContactPhone, CallDate)

    VALUES ('3', 'Name3', '34234344','2010-03-21');

    SELECT a.CustID, a.CustName, a.CustPhone

    from @CustMaster a

    SELECT * FROM @CustCalls

    I need to bring up the CustMsater table rows along with the latest phone number from CustCalls tables

    (based on the last call). If there is no call, it can show null value.

    The result will look like this...

    CustIDCustNameCustPhoneContactPhone

    1Customer11234534545454545

    2Customer212345787null

    3Customer33423434434234344

    Can you show me how to fix this. Thanks in advance

  • One way that comes to my mind is:

    select a.Custid ,a.CustName,a.CustPhone,a.ContactPhone from (

    select row_number() over (partition by a.Custid order by calldate desc ) rwnm,a.Custid ,a.CustName,a.CustPhone,b.CallDate,b.ContactPhone

    from @CustMaster a LEFT JOIN @CustCalls b ON a.CustID=b.CustID) a

    where rwnm=1

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Thanks Suresh.

  • This will give you what you want. The OUTER APPLY will out perform the row number solution if there are many calls per customer and there is an index on CallDate (which doesn't apply here since you are using table variables).

    SELECT CM.CustName, CM.CustName, CM.CustPhone

    , LC.ContactPhone, LC.CallDate

    FROM @CustMaster CM

    OUTER APPLY

    (SELECT TOP 1 ContactPhone, CallDate

    FROM @CustCalls CC

    WHERE CC.CustID = CM.CustID

    ORDER BY CallDate DESC

    ) LC -- Last Call

    Todd Fifield

  • Thank you Todd for your help.

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

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