代码之家 › 专栏 › 技术社区 › Andreas

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

row-number datediff compare sql-server-2012 sql-server

0

Andreas · 技术社区 · 8 年前

SEQ 来自 StartDate EndDate 第1行的日期比 @ValidationTime

例如,如果 @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

1 回复 | 直到 8 年前

1

Squirrel 8 年前

用例。何时检查哪一个更近

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