Calculate the fastest 5000 m time for the entire track

I have a cycling computer logging every second how far I've gone

A simplified version of the data looks like this:

 declare @DistanceTable Table
    (
      ID int,
      Time DateTime2,
      DistanceMeters float
    )

insert into @DistanceTable   values 
    (1, '2018-08-10 07:17:48', 3.8099999427795410),
    (2, '2018-08-10 07:17:49', 7.7600002288818359),
    (3, '2018-08-10 07:17:50', 12.3299999237060547),
    (4, '2018-08-10 07:17:51', 18.0000000000000000),
    (5, '2018-08-10 07:17:52', 24.8999996185302734),
    (6, '2018-08-10 07:17:53', 32.1599998474121094),
    (7, '2018-08-10 07:17:54', 40.7200012207031250),
    (8, '2018-08-10 07:17:55', 49.7599983215332031),
    (9, '2018-08-10 07:17:57', 68.6100006103515625),
    (10, '2018-08-10 07:17:58', 79.3199996948242188),
    (11, '2018-08-10 07:18:00', 100.1900024414062500),
    (12, '2018-08-10 07:18:02', 122.7099990844726563),
    (13, '2018-08-10 07:18:03', 134.1900024414062500),
    (14, '2018-08-10 07:18:04', 145.9199981689453125),
    (15, '2018-08-10 07:18:05', 158.4700012207031250),

    (16, '2018-08-10 07:24:04', 5003.4101562500000000), --5000 meters driven 
    (17, '2018-08-10 07:24:05', 5018.7797851562500000),
    (18, '2018-08-10 07:24:06', 5034.0498046875000000),
    (19, '2018-08-10 07:24:07', 5048.8901367187500000),
    (20, '2018-08-10 07:24:08', 5063.8798828125000000),
    (21, '2018-08-10 07:24:09', 5079.0200195312500000),
    (22, '2018-08-10 07:24:13', 5141.0600585937500000),
    (23, '2018-08-10 07:24:17', 5201.7500000000000000),
    (24, '2018-08-10 07:24:21', 5261.8798828125000000),
    (25, '2018-08-10 07:24:23', 5290.2900390625000000),
    (26, '2018-08-10 07:24:28', 5363.7099609375000000),
    (27, '2018-08-10 07:24:33', 5435.9101562500000000),
    (28, '2018-08-10 07:24:34', 5450.8901367187500000),
    (29, '2018-08-10 07:24:35', 5465.4199218750000000),
    (30, '2018-08-10 07:24:36', 5480.5400390625000000)

I'm trying to calculate the fastest 5000 m time for the entire track

So I want to calculate the time for the last 5000 meters for each record after 5000 m are driven

Solution

One problem that you have is that the difference is never exactly 5,000 meters. One approximation is to get the first value beyond 5,000 meters and use this for the calculation:

select top (1) dt.*, dt2.distance, dt2.time,
       (dt2.distance - dt.distance) as actual_distance,
       datediff(second, dt.time, dt2.time) as actual_time,
       (dt2.distance - dt.distance) / datediff(second, dt.time, dt2.time) as rate
from @DistanceTable dt cross apply
     (select top (1) dt2.*
      from @DistanceTable dt2
      where dt2.distance >= dt.distance + 5000 
      order by dt2.distance asc
     ) dt2
order by rate desc;

Your data points are close enough that the actual total distance would be like 5,009 meters, 5,002 meters and so on. That is probably good enough, so I'll stop here.

Actually interpolating the first and last legs to get an exact result is possible, but it would be a lot of effort for a very small improvement.