Only show the latest date in a selection of data

  • Hi there everyone

    I'm having problems using the MaxDate function in SQL and wondered if you could please help me?

    I have the following scenario whereby a ContactID field can appear several times and yet have different lines of SQL that show different renewal dates. This is for a membership organisation that has a different renewal date each year, just so you get the picture. So, I might have something like this:

    ContactID                 RenewalDate
    1                               01/07/2018
    1                               01/07/2017
    1                               01/07/2016
    2                               01/07/2015
    2                               01/07/2014
    3                               01/07/2018
    3                               01/07/2017
    3                               01/07/2016

    I'm trying to create a statement that will run through the Subscriptions table and only pull out the highlighted entries shown above (i.e. the latest date for each ContactID).

    I've tried various SQL scripts but none of them seem to work.

    Many thanks
    Jon

  • Please post table definitions and sample data (as INSERT statements) if you want a query written.

    If you just want advice, then you can use ROW_NUMBER, partition by ContactID Order by RenewalDate DESC and filter for that = 1

    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
  • Thanks Gail, but I'm not sure how to achieve that.
    I've been doing this (which I thought might work), but I still get multiple rows per ContactID

    SELECT ContactID, RenewalDate
    FROM SubscriptionMembers
    WHERE RenewalDate IN
    (SELECT MAX(RenewalDate)
    FROM SubscriptionMembers
    GROUP BY ContactID)
    ORDER BY ContactID

  • GilaMonster - Tuesday, January 2, 2018 8:38 AM

    Please post table definitions and sample data (as INSERT statements) if you want a query written.

    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
  • Here's a set of methods on how you can deal with versioned data. Instead of a GROUP BY, I'd suggest using a TOP 1 with an ORDER BY. That's generally going to result in superior performance.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Many thanks for your help everyone.

    Best wishes
    Jon

  • You should be able to use the MAX to get what you want, unless I'm not understanding your issue.

    DECLARE @sample TABLE
    (
        ContactID INT,
        RenewalDate DATE
    )
    ;

    INSERT INTO @sample
    SELECT 1, '01/07/2018'
    UNION ALL
    SELECT 1, '01/07/2017'
    UNION ALL
    SELECT 1, '01/07/2016'
    UNION ALL
    SELECT 2, '01/07/2015'
    UNION ALL
    SELECT 2, '01/07/2014'
    UNION ALL
    SELECT 3, '01/07/2018'
    UNION ALL
    SELECT 3, '01/07/2017'
    UNION ALL
    SELECT 3, '01/07/2016'

    SELECT ContactID, MAX(RenewalDate) AS RenewalDate
    FROM @sample
    GROUP BY ContactID
    ORDER BY ContactID
    Results:
    ContactID    RenewalDate
         1        2018-01-07
         2        2015-01-07
         3        2018-01-07

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • jon.clay 91459 - Tuesday, January 2, 2018 8:36 AM

    Hi there everyone

    I'm having problems using the MaxDate function in SQL and wondered if you could please help me?

    I have the following scenario whereby a ContactID field can appear several times and yet have different lines of SQL that show different renewal dates. This is for a membership organisation that has a different renewal date each year, just so you get the picture. So, I might have something like this:

    ContactID                 RenewalDate
    1                               01/07/2018
    1                               01/07/2017
    1                               01/07/2016
    2                               01/07/2015
    2                               01/07/2014
    3                               01/07/2018
    3                               01/07/2017
    3                               01/07/2016

    I'm trying to create a statement that will run through the Subscriptions table and only pull out the highlighted entries shown above (i.e. the latest date for each ContactID).

    I've tried various SQL scripts but none of them seem to work.

    Many thanks
    Jon

    Is this your requirement?


    create table renewal
    (
    contactid int,
    renewaldate date
    );
    insert into renewal values (1,'01/07/2016');
    insert into renewal values (1,'01/07/2017');
    insert into renewal values (1,'01/07/2018');
    insert into renewal values (2,'01/07/2014');
    insert into renewal values (2,'01/07/2015');
    select
    contactid,
    MAX(renewaldate)
    from renewal
    group by contactid

    Saravanan

Viewing 8 posts - 1 through 7 (of 7 total)

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