So here is what I try to achieve. I have a database with the following structure:
node_id, source_time, value
1 , 10:13:15 , 1
2 , 10:13:15 , 1
2 , 10:13:16 , 2
1 , 10:13:19 , 2
1 , 10:13:25 , 3
2 , 10:13:28 , 3
I want to have a SQL query to get the following output
time , value1, value2
10:13:15, 1 , 1
10:13:16, 1 , 2
10:13:19, 2 , 2
10:13:25, 3 , 2
10:13:28, 3 , 3
You see, the times are all times that occur from both nodes. But the values have to be filled in the gaps since node1 has no value for the time :16 and :28. I got it to the point where I get the 2 columns from one table. That was not the hard part.
SELECT T1.[value], T2.[value]
FROM [db1].[t_value_history] T1, [db1].[t_value_history] T2
WHERE ( T1.node_id = 1 AND T2.node_id = 2)
But the result doesn't look like the way I want it to be.
I found something with COALESCE and another table which holds the previous value. But that looked quite complicated for such a easy thing.
I would be happy to get any idea which function to use.
Thanks so far.
Edit: Changed the database, made a mistake on the last line.
Edit2: I am using SQL Server. Sorry for not clarifying this. Also the values are not neccessarily increasing. I just used increasing numbers in this example here.
This works in SQL Server. If you are certain that there is a value for both nodes for the minimum time then you could change the OUTER APPLY to a CROSS APPLY, which would perform better.
WITH times
AS ( SELECT DISTINCT
source_time
FROM dbo.t_value_history
)
SELECT t.source_time ,
n1.value ,
n2.value
FROM times AS t
OUTER APPLY ( SELECT TOP 1
h.value
FROM dbo.t_value_history AS h
WHERE h.node_id = 1
AND h.source_time <= t.source_time
ORDER BY h.source_time DESC
) AS n1
OUTER APPLY ( SELECT TOP 1
h.value
FROM dbo.t_value_history AS h
WHERE h.node_id = 2
AND h.source_time <= t.source_time
ORDER BY h.source_time DESC
) AS n2;