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?
4 Answers
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 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) 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 CREATE PROCEDURE CurrentDate @CurrentDate DATETIME AS BEGIN Select * from orders where OrderDate < @CurrentDate END GO 
