Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: Subquery to return Max Date of field with same Id

    The query itself works fine and is built with the following logic.

    Qry1: qrycall log and Qry2: Speedtolead is linked by phone number with the relationship Speedtolead has all results and is joined to calllog with results that only match phonenumber from Speedtolead

    The query works fine

    SELECT [2_SpeedtoLead_April_Phone Number Format].LeadDate, [2_SpeedtoLead_April_Phone Number Format].SyStudentID, [2_SpeedtoLead_April_Phone Number Format].StudentName, [2_SpeedtoLead_April_Phone Number Format].Phone, [2_SpeedtoLead_April_Phone Number Format].WorkPhone, [2_SpeedtoLead_April_Phone Number Format].otherphone, [2_SpeedtoLead_April_Phone Number Format].mobilenumber, [2_SpeedtoLead_April_Phone Number Format].StatusCode, [2_SpeedtoLead_April_Phone Number Format].StartDate, [2_SpeedtoLead_April_Phone Number Format].AdmRep, [2_SpeedtoLead_April_Phone Number Format].StatusDesc, [2_SpeedtoLead_April_Phone Number Format].LeadSrcDesc, [2_SpeedtoLead_April_Phone Number Format].AgencyDesc, [2_SpeedtoLead_April_Phone Number Format].RepTeam, [1_qryCallLog_AttemptorContact_Phonefmt].CallDirection, [1_qryCallLog_AttemptorContact_Phonefmt].LocalUserId, [1_qryCallLog_AttemptorContact_Phonefmt].PhoneNumberFinal, [1_qryCallLog_AttemptorContact_Phonefmt].InitiatedDate, [1_qryCallLog_AttemptorContact_Phonefmt].ConnectedDate, [1_qryCallLog_AttemptorContact_Phonefmt].CallDurationSeconds, [1_qryCallLog_AttemptorContact_Phonefmt].HoldDurationSeconds
    FROM [2_SpeedtoLead_April_Phone Number Format] LEFT JOIN 1_qryCallLog_AttemptorContact_Phonefmt ON [2_SpeedtoLead_April_Phone Number Format].PhoneFinal = [1_qryCallLog_AttemptorContact_Phonefmt].PhoneNumberFinal;


    The only issue is that in the qrycalllog their are multiple items for the same phone number as their are different contacts time/date in the InitiatedDate field.
    I am trying to make it so that InitiatedDate field only brings back the latest(max) result for that phone number.

    I first tried doing a MAX in the intiateddate fiend in the query design but it did not work as I got an agregate error.
    I then tried creating a subquery which worked with no errors but had zero results.
    The SQL is as follows:
    (See the end for the added subquery)


    SELECT [2_SpeedtoLead_April_Phone Number Format].LeadDate, [2_SpeedtoLead_April_Phone Number Format].SyStudentID, [2_SpeedtoLead_April_Phone Number Format].StudentName, [2_SpeedtoLead_April_Phone Number Format].Phone, [2_SpeedtoLead_April_Phone Number Format].WorkPhone, [2_SpeedtoLead_April_Phone Number Format].otherphone, [2_SpeedtoLead_April_Phone Number Format].mobilenumber, [2_SpeedtoLead_April_Phone Number Format].StatusCode, [2_SpeedtoLead_April_Phone Number Format].StartDate, [2_SpeedtoLead_April_Phone Number Format].AdmRep, [2_SpeedtoLead_April_Phone Number Format].StatusDesc, [2_SpeedtoLead_April_Phone Number Format].LeadSrcDesc, [2_SpeedtoLead_April_Phone Number Format].AgencyDesc, [2_SpeedtoLead_April_Phone Number Format].RepTeam, [1_qryCallLog_AttemptorContact_Phonefmt].CallDirection, [1_qryCallLog_AttemptorContact_Phonefmt].LocalUserId, [1_qryCallLog_AttemptorContact_Phonefmt].PhoneNumberFinal, [1_qryCallLog_AttemptorContact_Phonefmt].InitiatedDate, [1_qryCallLog_AttemptorContact_Phonefmt].ConnectedDate, [1_qryCallLog_AttemptorContact_Phonefmt].CallDurationSeconds, [1_qryCallLog_AttemptorContact_Phonefmt].HoldDurationSeconds
    FROM [2_SpeedtoLead_April_Phone Number Format] LEFT JOIN 1_qryCallLog_AttemptorContact_Phonefmt ON [2_SpeedtoLead_April_Phone Number Format].PhoneFinal = [1_qryCallLog_AttemptorContact_Phonefmt].PhoneNumberFinal

    WHERE [1_qryCallLog_AttemptorContact_Phonefmt].InitiatedDate =
    (SELECT MAX([1_qryCallLog_AttemptorContact_Phonefmt].InitiatedDate)
    FROM [1_qryCallLog_AttemptorContact_Phonefmt]);




    Please help

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try:

    WITH S1 (PhoneNumberFinal, CallDirection, LocalUserId, PhoneNumberFinal, InitiatedDate, ConnectedDate, CallDurationSeconds, HoldDurationSeconds)AS(SELECT PhoneNumberFinal, CallDirection, LocalUserId, PhoneNumberFinal, InitiatedDate, ConnectedDate, CallDurationSeconds, HoldDurationSeconds, ROW_NUMBER() over (PARTITION by PhoneNumberFinal Order By InitiatedDate DESC) as RowNum FROM [1_qryCallLog_AttemptorContact_Phonefmt])SELECT S2.LeadDate, S2.SyStudentID, S2.StudentName, S2.Phone, S2.WorkPhone, S2.otherphone, S2.mobilenumber, S2.StatusCode, S2.StartDate, S2.AdmRep, S2.StatusDesc, S2.LeadSrcDesc, S2.AgencyDesc, S2.RepTeam, S1.CallDirection, S1.LocalUserId, S1.PhoneNumberFinal, S1.InitiatedDate, S1.ConnectedDate, S1.CallDurationSeconds, S1.HoldDurationSeconds FROM [2_SpeedtoLead_April_Phone Number Format] as S2 LEFT JOIN S1 ON S2.PhoneFinal = S1.PhoneNumberFinal WHERE S1.rownum = 1;

    I tried to mimic the way you presented your SQL code. Do you really like it this way? I find it unreadable.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2011
    Posts
    3
    Thank you I will try this.

    I agree, I copied it right from Access SQL the way it writes it. I should have separated it better. I appreciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •