Sample for 9104598

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Curent time input example 08:00 AM


DECLARE @CurrentTime as  time 
SET @CurrentTime = '##CurrentTime##'

DECLARE @Schedules  table  (id int, arrival time)
INSERT INTO @Schedules
VALUES (1 , '06:16:00' ),
(2, '06:17:00' ),
(3, '07:19:00'),
(4, '08:21:00'),
(5, '10:22:00'),
(6, '13:21:00'),
(7, '20:22:00')

DECLARE @closestTime as time
SELECT TOP 1 @closestTime = arrival  FROM @Schedules ORDER BY ABS(DATEDIFF(mi, @CurrentTime ,arrival))

;WITH cte 
     AS (SELECT id, 
                arrival, 
                Row_number() OVER (PARTITION BY (CASE WHEN @closestTime > arrival THEN 1 
                                                      WHEN @closestTime < arrival THEN 2 END) 
                                   ORDER BY Abs(Datediff(mi, @closestTime, arrival))) rn 
         FROM   @Schedules) 
SELECT * 
FROM   cte 
WHERE  rn < 3 
        OR arrival = @closestTime 
ORDER  BY id

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results