How can I select the closest value less and more than a given value?

Question

I have two tables, one for values, one for location, and I'm trying to interpolate the location. The tables have been simplified to the following:

CREATE TABLE value( Timestamp DATETIME2, Value float NOT NULL, PRIMARY KEY(Timestamp) ); CREATE TABLE location( Timestamp DATETIME2, Position INT NOT NULL, PRIMARY KEY(Timestamp) ); INSERT INTO value VALUES ('2011/12/1 16:55:01', 1), ('2011/12/1 16:55:02', 5), ('2011/12/1 16:55:05', 10), ('2011/12/1 16:55:08', 6); INSERT INTO location VALUES ('2011/12/1 16:55:00', 0), ('2011/12/1 16:55:05', 10), ('2011/12/1 16:55:10', 5) 

Expected results:

 TimeStamp, Value, LowerTime, LowerLocation, UpperTime, UpperLocation 2011-12-01 16:55:01, 1, 2011-12-01 16:55:00, 0, 2011-12-01 16:55:05, 10 2011-12-01 16:55:02, 5, 2011-12-01 16:55:00, 0, 2011-12-01 16:55:05, 10 2011-12-01 16:55:05, 10, 2011-12-01 16:55:05, 10, 2011-12-01 16:55:05, 10 2011-12-01 16:55:08, 6, 2011-12-01 16:55:05, 10, 2011-12-01 16:55:10, 5 

(Keep in mind that these are simplified data examples to get an idea of ​​the query I'm trying to execute.)

To do the interpolation, I need to find out the time and places before and after the given time values. I am currently doing this with a query that looks like this:

 SELECT V.Timestamp, V.Value, (SELECT MAX(Timestamp) FROM dbo.location WHERE Timestamp <= V.Timestamp) as LowerTime, (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp <= V.Timestamp ORDER BY timestamp DESC) as LowerLocation, (SELECT MIN(Timestamp) FROM dbo.location WHERE Timestamp >= V.Timestamp) as UpperTime, (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp >= V.Timestamp ORDER BY timestamp ASC) as UpperLocation FROM dbo.value V 

Now it works, but it obviously does a lot of work. I think there should be a simplification of the request that I miss, but I played with it all morning and did not come up with anything concrete. Hope someone has a better idea.

I'm currently studying whether there is a way to calculate the LowerTime and UpperTime values ​​and use them when determining locations. Sort of:

 SELECT V.Timestamp, V.Value, (SELECT MAX(Timestamp) FROM dbo.location WHERE Timestamp <= V.Timestamp) as LowerTime, (SELECT Position FROM dbo.location WHERE Timestamp = LowerTime) as LowerLocation, (SELECT MIN(Timestamp) FROM dbo.location WHERE Timestamp >= V.Timestamp) as UpperTime, (SELECT Position FROM dbo.location WHERE Timestamp = UpperTime) as UpperLocation FROM dbo.value V 

but it does not work.

EDIT1: Updated request as suggested. However, no visible changes in runtime.

EDIT2: Added my thoughts on the approach I'm trying to do now.

Answer 1

For simplicity, you can at least use the functions MAX() and MIN() to query fields timestamp instead of the TOP 1 and ORDER BY .

Full request will be

 SELECT V.Timestamp, V.Value, (SELECT MAX(Timestamp) FROM dbo.location WHERE Timestamp <= V.Timestamp) as LowerTime, (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp <= V.Timestamp ORDER BY timestamp DESC) as LowerLocation, (SELECT MIN(Timestamp) FROM dbo.location WHERE Timestamp >= V.Timestamp) as UpperTime, (SELECT TOP 1 Position FROM dbo.location WHERE Timestamp >= V.Timestamp ORDER BY timestamp ASC) as UpperLocation FROM dbo.value V 

Answer 2

This might do the trick (although I think the connection looks pretty ugly):

 ;with OrderedLocations as ( select v.Timestamp, v.Value, l.Timestamp as tsl, l.Position, ROW_NUMBER() OVER (PARTITION BY v.Timestamp ORDER BY CASE WHEN l.Timestamp <= v.Timestamp THEN l.Timestamp ELSE '00010101' END desc) as PrevRN, ROW_NUMBER() OVER (PARTITION BY v.Timestamp ORDER BY CASE WHEN l.Timestamp >= v.Timestamp THEN l.Timestamp ELSE '99991231' END asc) as NextRN from value v cross join location l ) select ol1.Timestamp, ol1.Value, ol1.tsl, ol1.Position, ol2.tsl, ol2.Position from OrderedLocations ol1 inner join OrderedLocations ol2 on ol1.Timestamp = ol2.Timestamp and ol1.Value = ol2.Value where ol1.PrevRN = 1 and ol2.NextRN = 1 

Unfortunately, as with most efficiency / performance questions, the answer is usually trying to use many different combinations with your actual tables and data and measure how each of them performs.

---

An alternative (avoiding combining) using the same CTE as above would be the following:

 SELECT Timestamp,Value, MAX(CASE WHEN PrevRN=1 THEN tsl END),MAX(CASE WHEN PrevRN=1 then Position END), MAX(CASE WHEN NextRN=1 THEN tsl END),MAX(CASE WHEN NextRN=1 then Position END) FROM OrderedLocations where PrevRN=1 or NextRN=1 group by Timestamp,Value 

---

CTE ( OrderedLocations ) trying to build a set of lines, where each line of each row is mapped locations in value . For each resulting string, we calculate two ROW_NUMBER - line number where we count all rows with less than or equal to timestamp ( PrevRN ) in descending order, and the other, where we count all the rows with a greater or equal to timestamp ( NextRN ) in ascending order. Then we build our final result by simply looking at those rows where one of these row numbers is 1.