Post reply

Compare dates

  • August 2, 2011 at 9:44 am

    #254797

    I have some data, with differents date.

    Without making a subquery, is there a function on 2008r2 to compare which is the nearest date on the table to today?

    Thank you very much.

  • August 2, 2011 at 9:58 am

    #1362294

    -Syd- (8/2/2011)

    I have some data, with differents date.

    Without making a subquery, is there a function on 2008r2 to compare which is the nearest date on the table to today?

    No silver bullet as far as I know.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • August 2, 2011 at 10:02 am

    #1362300

    Thank you. So if there any way to compare which the nearest date on a table?

    But it cant be order DESC by date place a TOP 1 on the select clause.

  • August 2, 2011 at 10:10 am

    #1362310

    -Syd- (8/2/2011)

    Thank you. So if there any way to compare which the nearest date on a table?

    But it cant be order DESC by date place a TOP 1 on the select clause.

    That would certainly work.

    Let's say you are looking for the previous "order" of a particular "customer" then subquery should select top 1 order_date from order_table where customer = "customer you are looking for" order by order_date desc. an Index on (customer,order_date) would provide the best performance for this particular solution.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • August 2, 2011 at 10:22 am

    #1362321

    -- make a sample table with some dates in it

    DROP TABLE #Dates

    CREATE TABLE #Dates (RandomDate DATETIME)

    INSERT INTO #Dates (RandomDate)

    SELECT Dateval = GETDATE() + n.RN

    FROM (SELECT TOP 100 RN = ROW_NUMBER() OVER(ORDER BY NAME) - 50 FROM sys.COLUMNS) n

    -- eyeball it

    --SELECT * FROM #Dates

    -- get nearest date before and after GETDATE()

    SELECT *

    FROM (

    SELECT RowID = 1, ACloseDate = MAX(RandomDate) FROM #Dates WHERE RandomDate < GETDATE()

    UNION

    SELECT 2, MIN(RandomDate) FROM #Dates WHERE RandomDate > GETDATE()

    ) d

    “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

  • August 2, 2011 at 10:51 am

    #1362348

    Thank you all very much.

    Regards.

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

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