I need to find 2 values and related columns where a given number either sits between the column values or is either of the 2 values. Really confusing but here is a table:

I have a car_id 2 which is going at speed 12 and I want to get a fuel value. I need to get the value of the next higher and the next lower and use those to calculate the fuel for speed 12 for car_id 2. ids are not in any useful order and speed is not always incremental. I have a stored procedure which passes the values of car_id and speed and delivers the value after a calculation but I would like to just use a query or build a view which can perform this function too.

Hope I haven't confused you too much and thank you for your help.

CodePudding user response：

DROP TABLE IF EXISTS #data;
CREATE TABLE #data
(
    id INT,
    car_id INT,
    speed INT,
    fuel INT
);

INSERT INTO #data
VALUES
(1  ,1, 5   ,10),
(2  ,1, 10  ,20),
(3  ,1, 15  ,29),
(4  ,1, 20  ,37),
(5  ,1, 25  ,45),
(6  ,2, 5   , 7),
(7  ,2, 10  ,14),
(8  ,2, 15  ,20),
(9  ,2, 20  ,26),
(10 ,2, 25  ,31);

DECLARE @speed INT = 12;
DECLARE @car_id INT = 2;

WITH cte AS (
    SELECT speed, 
        fuel, 
        LEAD(speed,1) OVER (PARTITION BY car_id ORDER BY speed) speed2,
        LEAD(fuel,1)  OVER (PARTITION BY car_id ORDER BY speed) fuel2,
        ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY speed) rn
    FROM #data
    WHERE car_id = @car_id
), rts AS 
(
    SELECT MAX(rn) rowtoselect
    FROM cte 
    WHERE cte.speed <= @speed
)
SELECT cte.speed, cte.fuel, cte.speed2, cte.fuel2
FROM cte 
INNER JOIN rts ON cte.rn = rts.rowtoselect