SQL Server 2012：查找多列和行的最近日期

Question

我正在努力寻找从多列和多行中获取最接近值的最佳方法。在下面的代码中，我只从StartDate获得SEQ编号，但我还需要知道与@ValidationTime相比，第1行的EndDate是否比开始日期更近。

例如，如果@Validationtime为10:10，则第1行是最接近的值，但10:20更接近第2行。

最后，我将只处理"最近的插槽"(1行(。

我希望我的解释不太混乱

DECLARE @ValidationTime as datetime
SET @ValidationTime = '2017-08-29 10:10:00.000'
--SET @ValidationTime = '2017-08-29 10:20:00.000'
DECLARE @table TABLE (ID INT, StartDate datetime, EndDate datetime);
INSERT INTO @table 
VALUES (1, '2017-08-29 08:00:00.000', '2017-08-29 10:00:00.000'),    
(1, '2017-08-29 10:30:00.000', '2017-08-29 21:00:00.000'),    
(1, '2017-08-30 08:00:00.000', '2017-08-30 10:00:00.000'),
(1, '2017-08-30 19:00:00.000', '2017-08-30 21:00:00.000');
SELECT 
ID, StartDate, EndDate, 
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ABS(DATEDIFF(MINUTE, StartDate, @ValidationTime))) AS SEQ
FROM 
@table

Answer 1

使用CASE。何时检查哪个更接近

SELECT  ID, 
StartDate, 
EndDate, 
ROW_NUMBER() OVER ( Partition by  ID
Order by CASE   WHEN ABS(DateDiff(MINUTE,StartDate,@ValidationTime)) < ABS(DateDiff(MINUTE,EndDate,@ValidationTime))
THEN ABS(DateDiff(MINUTE,StartDate,@ValidationTime))
ELSE ABS(DateDiff(MINUTE,EndDate,@ValidationTime))
END
) AS SEQ
FROM    @table