Post reply

Getting latest date from columns

  • December 10, 2010 at 8:21 am

    #388413

    Hi all,

    I'm wondering what is the best way to get the latest datetime from several columns.

    The query is quite complex and speed is an issue, but I end up with something like:

    SubjectID, Date_A, Date_B, Date_C, Date_D, Date_E, Date_F, Date_G

    with multiple rows returned.

    What i want is to add another column, lets call it Latest_Date, which is the latest datetime from all the datetime columns - so if I had:

    SubjectID, Date_A, Date_B, Date_C, Date_D, Date_E, Date_F, Date_G

    123, 1/2/2010 09:00:00, 1/3/2010 08:00:00, 1/1/2010 17:00:00, 2/1/2010 16:00:00, 1/8/2010 04:00:00, 1/18/2010 03:00:00, 1/23/2010 07:00:00

    Then I want the Latest_date column to be 2/1/2010 16:00:00

    Up to now I've been trying to use nested case statements, but that can't possibly be the best approach here..

    Any ideas would be appreciated

    Thanks

    --------------------------

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • December 10, 2010 at 8:34 am

    #1261071

    Unpivot the data , either manually using case or the UNPIVOT operator

    and then its simply top(1) ordered by DateCol desc


    Clear Sky SQL
    My Blog[/url]

  • December 10, 2010 at 9:21 am

    #1261104

    Thanks Dave - that worked like a charm - I actually went back to one of the views that was used in the query and added the latest_date there and unpivoted in the view rather than my query - results are as expected.

    Thanks again

    --------------------------

    I long for a job where my databases dont have any pesky users accessing them 🙂

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

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