Post reply

Select the last two previous dates from a Dates Table

  • September 15, 2005 at 7:33 am

    #165953

    I need to compare a record with a date to the last two dates (two previous dates) in a Dates Table. I want to use my Dates Table because it takes Holidays and Weekends into consideration. Below, I am using the DATEADD Function with a hardcoded date. How can I replace the DATEADD Function with the last two dates in the Date Table. Do I need to read (Select) the Dates Table twice and store the two dates into two local variables? I know I can get the last date by using the Max(Date) Function. How would I get the next to last date from the Dates Table? (Would the Top Function work?)

    DECLARE @BusinessDate AS DateTime

    SET @BusinessDate = '07/29/05'

     SELECT 

     *

     FROM Table A

     --Note I need to pull data from the last 2 previous business days

     WHERE A.AsOfDate > DATEADD (dd, -1, @BusinessDate)

    Thanks in advance, Kevin.

  • September 15, 2005 at 7:51 am

    #590138

    Something like this??

    Select top 2 Dates from dbo.Calendar where Dates < @BusinessDate order by Dates Desc

  • September 15, 2005 at 8:06 am

    #590146

    "How would I get the next to last date from the Dates Table? (Would the Top Function work?) "

    select max(date) from dates where date < (select max(d1.date) from dates d1)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • September 15, 2005 at 10:27 am

    #590224

    select max(Latest2.[Date]) as MostRecent

    ,min(Latest2.[Date]) as PriorDate

    from (select top 2 [Date] from Dates

    where [Date] <= @BusinessDate

    order by [Date] DESC

    ) Latest2

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

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