Finding the 2 rows where a given value sits between 2 values in a table

Derek Jee 0
Viewed 39

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.

id car_id speed fuel
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

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

sql-server
2 Answers
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
James Casey0

select * from car_data

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

with cte as
(
  select * from car_data
  union all
  select @car_id,@speed,null
)
select car_id,speed,y1+(y2-y1)*(speed-x1)/(x2-x1) as fuel from 
( select *,lead(speed,1) over(partition by car_id order by speed) as x1,lag(speed,1) over(partition by car_id order by speed) as x2,
  lead(fuel,1) over(partition by car_id order by speed) as y1, lag(fuel,1) over(partition by car_id order by speed) as y2
  from cte) tab
where speed=@speed and car_id=@car_id
Aswin0
Related