Trova la data più vicina in SQL Server

Ho una tabella dbo.X con la column Y DateTime column Y che potrebbe contenere centinaia di record.

My Stored Procedure ha parametro @CurrentDate , voglio trovare la data nella column Y nella tabella sopra dbo.X che è inferiore e più vicina a @CurrentDate.

Come trovarlo?

La clausola where corrisponderà a tutte le righe con data inferiore a @CurrentDate e, dato che sono ordinate in modo discendente, la TOP 1 sarà la data più vicina alla data corrente.

 SELECT TOP 1 * FROM x WHERE x.date < @CurrentDate ORDER BY x.date DESC 

Utilizzare DateDiff e ordinare il risultato in base al numero di giorni o secondi tra quella data e l’input

Qualcosa come questo

  select top 1 rowId, dateCol, datediff(second, @CurrentDate, dateCol) as SecondsBetweenDates from myTable where dateCol < @currentDate order by datediff(second, @CurrentDate, dateCol) 

Ho una soluzione migliore per questo problema, penso.

Mostrerò alcune immagini per supportare e spiegare la soluzione finale.

Background Nella mia soluzione ho una tabella di tassi FX. Questi rappresentano i tassi di mercato per diverse valute. Tuttavia, il nostro fornitore di servizi ha avuto un problema con il feed rate e in quanto tali alcune tariffe hanno valori zero. Voglio riempire i dati mancanti con i tassi per quella stessa valuta che sono i più vicini nel tempo al tasso mancante. Fondamentalmente voglio ottenere il RateId per la tariffa non zero più vicina che sostituirò. (Questo non è mostrato qui nel mio esempio.)

1) Quindi, per avviare consente di identificare le informazioni sui tassi mancanti:

Query che mostra i miei tassi mancanti, ovvero ha un valore di tasso pari a zero

2) Successivamente consente di identificare i tassi che non mancano. Query che mostra i tassi che non mancano

3) Questa query è dove avviene la magia. Qui ho fatto un’ipotesi che può essere rimossa, ma è stata aggiunta per migliorare l’efficienza / le prestazioni della query. L’assunto sulla linea 26 è che mi aspetto di trovare una transazione sostitutiva lo stesso giorno della transazione mancante / zero. La magia accade è la riga 23: la funzione Row_Number aggiunge un numero automatico che inizia a 1 per la differenza di tempo più breve tra la transazione mancante e quella mancante. La transazione successiva più vicina ha un rownum di 2 ecc.

Tieni presente che nella riga 25 devo aderire alle valute in modo da non mismatchare i tipi di valuta. Questo è che non voglio sostituire una valuta AUD con valori in CHF. Voglio le valute di corrispondenza più vicine.

Combinare i due set di dati con row_number per identificare la transazione più vicina

4) Infine, consente di ottenere dati in cui RowNum è 1 La query finale

La query completa della query è la seguente;

  ; 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