Re: A new proof of the superiority of set oriented approaches: numerical/time serie linear interpolation

Odd thought, not fully worked out. The choice of representation can be important in a data-centric solution. If you had designed the table like this, with accumulated data, like this: :

CREATE TABLE TrackReadings
(station_nbr INTEGER NOT NULL PRIMARY KEY,  accum_dist DECIMAL (6,3) NOT NULL -- in meters?

    CHECK (accum_dist >= 0.000),
 accum_time INTEGER DEFAULT 0 NOT NULL -- in seconds

    CHECK (accum_time >= 0));

With starting data like this:

TrackReadings
station_nbr accum_dist accum_time

00  0.000  0 -- start of data series
01  0.289  0
02  0.482  0
03  0.783  0
04  0.914  0
05  1.358  0
06  1.518  0
07  2.183  0
08  2.769  240

I numbered the stations from zero up just so the math looks "more mathematical" and I get an easier WHERE clause in the UPDATE, if that makes sense. The other columns are accumulated values. This is easy to do with the new "SUM() OVER()" functions in a VIEW if you cannot collect the data this way in the first place.

total distance = (SELECT MAX(accum_dist) FROM TrackReadings) total elapsed time = (SELECT MAX(accum_time) FROM TrackReadings)

Use the distance covered at this station to the total distance as the percentage for the interpolation, something like this:

UPDATE TrackReadings

   SET accum_time

• (TrackReadings.accum_dist / (SELECT MAX(accum_dist) FROM TrackReadings AS T1 WHERE T1.station_nbr = TrackReadings.station_nbr) * (SELECT MAX(accum_time) FROM TrackReadings AS T2) WHERE T2.station_nbr = TrackReadings.station_nbr)); WHERE station_nbr > 0;

Now we create a VIEW that will renumber the stations, and use the DATEADD with a base time to get the timestamps. Received on Tue May 01 2007 - 23:24:51 CEST