45

I have a table dbo.X with DateTime column Y which may have hundreds of records.

My Stored Procedure has parameter @CurrentDate, I want to find out the date in the column Y in above table dbo.X which is less than and closest to @CurrentDate.

How to find it?

nbrooks
  • 18,126
  • 5
  • 54
  • 66
MaxRecursion
  • 4,773
  • 12
  • 42
  • 76

3 Answers3

88

The where clause will match all rows with date less than @CurrentDate and, since they are ordered descendantly, the TOP 1 will be the closest date to the current date.

SELECT TOP 1 *
FROM x
WHERE x.date < @CurrentDate
ORDER BY x.date DESC
ederbf
  • 1,713
  • 1
  • 13
  • 18
  • 1
    Depends on the database design. I suggest that you try setting up an index on your table for the date field, so the system will be able to go directly for the value that matches the query. – ederbf Dec 25 '12 at 11:32
  • I would like to ask a follow-up question: how would you find the most recent date _for every Customer?_ [This article](http://blog.metricadb.com/post/54735021407/finding-the-most-recent-purchase-by-each-customer) recommends using Windowed functions. Would you agree? – kmote Aug 05 '15 at 15:20
  • 1
    @kmote if you only want customer ID and the corresponding date, then for simplicity I would go for the 'SELECT x.CustomerID, MAX(x.date) FROM x GROUP BY x.CustomerID' alternative proposed in the article you link. But if you want to retrieve more information and/or perform more calculations other than getting the most recent date, then windowed functions seem a good alternative. I don't have much experience to tell you how this functions do performance-wise, but I would definitely try. – ederbf Aug 06 '15 at 10:58
16

Use DateDiff and order your result by how many days or seconds are between that date and what the Input was

Something like this

    select top 1 rowId, dateCol, datediff(second, @CurrentDate, dateCol) as SecondsBetweenDates
    from myTable
    where dateCol < @currentDate
    order by datediff(second, @CurrentDate, dateCol)
Mikey Mouse
  • 2,968
  • 2
  • 26
  • 44
  • 3
    In ORDER BY, you can reference columns by their aliases (i.e. you can `ORDER BY SecondsBetweenDate`). However, you are sorting the rows by an expression rather than by a column. That is likely to make the query non-[sargable](http://en.wikipedia.org/wiki/Sargable "Sargable (Wikipedia)"). Just a note. – Andriy M Dec 26 '12 at 18:09
  • You could get negative numbers in the datediff and then the closest to the result you want is somewhere in the middle. – chris dorn Oct 22 '18 at 15:37
  • 2
    may want to take absolute value `ABS` of the diff to handle negative values if you don't know if your first date will be less than or greater than the second date. – JohnnyFun Mar 18 '19 at 21:07
2

I have a better solution for this problem i think.

I will show a few images to support and explain the final solution.

Background In my solution I have a table of FX Rates. These represent market rates for different currencies. However, our service provider has had a problem with the rate feed and as such some rates have zero values. I want to fill the missing data with rates for that same currency that as closest in time to the missing rate. Basically I want to get the RateId for the nearest non zero rate which I will then substitute. (This is not shown here in my example.)

1) So to start off lets identify the missing rates information:

Query showing my missing rates i.e. have a rate value of zero

2) Next lets identify rates that are not missing. Query showing rates that are not missing

3) This query is where the magic happens. I have made an assumption here which can be removed but was added to improve the efficiency/performance of the query. The assumption on line 26 is that I expect to find a substitute transaction on the same day as that of the missing / zero transaction. The magic happens is line 23: The Row_Number function adds an auto number starting at 1 for the shortest time difference between the missing and non missing transaction. The next closest transaction has a rownum of 2 etc.

Please note that in line 25 I must join the currencies so that I do not mismatch the currency types. That is I don't want to substitute a AUD currency with CHF values. I want the closest matching currencies.

Combining the two data sets with a row_number to identify nearest transaction

4) Finally, lets get data where the RowNum is 1 The final query

The query full query is as follows;

    ; with cte_zero_rates as
(
        Select      * 
        from        fxrates
        where       (spot_exp = 0 or spot_exp = 0) 
),
cte_non_zero_rates as
(
        Select      * 
        from        fxrates
        where       (spot_exp > 0 and spot_exp > 0) 
)
,cte_Nearest_Transaction as
(
        select       z.FXRatesID    as Zero_FXRatesID
                    ,z.importDate   as Zero_importDate
                    ,z.currency     as Zero_Currency
                    ,nz.currency    as NonZero_Currency
                    ,nz.FXRatesID   as NonZero_FXRatesID
                    ,nz.spot_imp
                    ,nz.importDate  as NonZero_importDate
                    ,DATEDIFF(ss, z.importDate, nz.importDate) as TimeDifferece
                    ,ROW_NUMBER() Over(partition by z.FXRatesID order by abs(DATEDIFF(ss, z.importDate, nz.importDate)) asc) as RowNum
        from        cte_zero_rates z 
        left join   cte_non_zero_rates nz on nz.currency = z.currency
                    and cast(nz.importDate as date) = cast(z.importDate as date)
        --order by  z.currency desc, z.importDate desc
)
select           n.Zero_FXRatesID
                ,n.Zero_Currency
                ,n.Zero_importDate
                ,n.NonZero_importDate
                ,DATEDIFF(s, n.NonZero_importDate,n.Zero_importDate) as Delay_In_Seconds
                ,n.NonZero_Currency
                ,n.NonZero_FXRatesID
 from           cte_Nearest_Transaction n
 where          n.RowNum = 1
                and n.NonZero_FXRatesID is not null
 order by       n.Zero_Currency, n.NonZero_importDate
Craig Gers
  • 544
  • 3
  • 9