У меня есть таблица dbo.X с DateTime column Y, которая может содержать сотни записей.
В моей хранимой процедуре есть параметр @CurrentDate, я хочу узнать дату в column Y в приведенной выше таблице dbo.X, которая меньше и ближе к @CurrentDate.
Как его найти?
Предложение where будет соответствовать всем строкам с датой, меньшей чем @CurrentDate, и, поскольку они заказываются по наследству, TOP 1 будет ближайшей датой к текущей дате.
SELECT TOP 1 *
FROM x
WHERE x.date < @CurrentDate
ORDER BY x.date DESC
Используйте DateDiff и закажите свой результат по количеству дней или секунд между этой датой и тем, что был входом
Что-то вроде этого
select top 1 rowId, dateCol, datediff(second, @CurrentDate, dateCol) as SecondsBetweenDates
from myTable
where dateCol < @currentDate
order by datediff(second, @CurrentDate, dateCol)
У меня есть лучшее решение для этой проблемы, я думаю.
Я покажу несколько изображений для поддержки и объяснения окончательного решения.
Фон В моем решении у меня есть таблица курсов валют. Они представляют собой рыночные ставки для разных валют. Однако у нашего поставщика услуг возникла проблема с подачей ставки, и, как следствие, некоторые ставки имеют нулевые значения. Я хочу заполнить недостающие данные тарифами для той же самой валюты, которая как можно ближе к отсутствующей ставке. В принципе, я хочу получить RateId для ближайшей нулевой скорости, которую я затем заменим. (Это не показано здесь в моем примере.)
1) Итак, чтобы начать, укажите информацию о недостающих скоростях:
Запрос, показывающий мои недостающие ставки, то есть значение скорости, равное нулю
2) Далее указывается скорость, которую не хватает. Запрос, показывающий скорости, которые не пропущены
3) Этот запрос - это то, где происходит волшебство. Я сделал здесь предположение, которое можно удалить, но было добавлено для повышения эффективности/производительности запроса. Предположение на строке 26 состоит в том, что я ожидаю найти заменяющую транзакцию в тот же день, что и транзакция с недостающим/нулевым. Магия происходит в строке 23: Функция Row_Number добавляет автоматический номер, начинающийся с 1 для кратчайшего разницы во времени между отсутствующей и отсутствующей транзакцией. Следующая ближайшая транзакция имеет rownum из 2 и т.д.
Обратите внимание, что в строке 25 я должен присоединиться к валютам, чтобы я не рассортировал типы валют. То есть я не хочу подставлять валюту AUD значениями CHF. Я хочу, чтобы самые близкие соответствующие валюты.
Сочетание двух наборов данных с номером row_number для определения ближайшей транзакции
4) Наконец, давайте получим данные, где RowNum равен 1 Окончательный запрос
Запрос полного запроса выглядит следующим образом:
; 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