Post reply

Retrieving latest date from 2 fields in table

  • November 18, 2008 at 4:04 am

    #202950

    I need to write some SQL to determine when a record in a table (UsedVehicles) was last updated. I have records of used cars and if my client adds a car, I store today's date (getdate()) in the DateCreated field for that record and if he amends a car, I store getdate() in the DateAmended field. In the Used vehicles section of the website, I need to determine the date the site was last updated, therefore, I need to retrieve the latest date between the 2 date fields, bearing in mind that for many records, the DateAmended will be null. I know I could do 2 separate queries but surely there must be an easier way. Any help would be much appreciated.

    Thanks

    Lorna

  • November 18, 2008 at 4:28 am

    #899776

    Hi Lorna

    something like this should do the trick:

    -- If the row has been amended, then the amended date will not be null; use the amend date.

    -- If the row has not been amended then the amended date will be null; use the create date.

    SELECT ... ISNULL(DateAmended, DateCreated) AS [latest date]

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 18, 2008 at 4:55 am

    #899788

    Case when Date_Amended is null then Date_Created else Date_amended end as Latest_Date

  • November 18, 2008 at 5:33 am

    #899822

    Thank you very much - will try this and post an example in case anyone else is interested 🙂

  • November 18, 2008 at 5:37 am

    #899828

    Lorna Wheelhouse (11/18/2008)

    Thank you very much - will try this and post an example in case anyone else is interested 🙂

    You're welcome Lorna. Incidentally, both statements produce the same result...

    ISNULL(DateAmended, DateCreated) AS [latest date]

    Case when Date_Amended is null then Date_Created else Date_amended end as Latest_Date

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 18, 2008 at 7:10 am

    #899895

    Just a warning... The proposed solutions are probably OK in this case, where DateCreated of a row will never change (at least one can assume it). It wouldn't work if the "original" date (DateCreated in posted example) is not creation date and gets updated in some situation.

    Safer solution would be for example:

    SELECT CASE WHEN ISNULL(DateAmended, DateCreated) > DateCreated THEN DateAmended ELSE DateCreated END as last_date

    FROM ...

  • November 19, 2008 at 4:05 am

    #900415

    Great thanks - works well. Only thing is it returns lots of values but I can sort DESC and then take the first record value -unless anyone knows an easier way?

    Much appreciated.

    Lorna

  • November 19, 2008 at 4:10 am

    #900416

    Try this:

    ORDER BY CASE WHEN ISNULL(DateAmended, DateCreated) > DateCreated THEN DateAmended ELSE DateCreated END DESC

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 19, 2008 at 6:15 am

    #900467

    Thanks everyone - learning loads today from you guys 🙂

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

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