select *
from tbl_a, tbl_b
where floor(tbl_b.engaged_time) = tbl_a.time_in_sec and tbl_b.set_id = tbl_a.run_id
select *
from tbl_a, tbl_b
where ceiling(tbl_b.engaged_time
) = tbl_a.time_in_sec and tbl_b.set_id = tbl_a.run_id
Essentially what I am trying to do is take the event data that occurs at the floor and the ceiling time and generate a single recordset from that based on the engaged time to determine the floor and ceiling.
tbl_a contains event data by whole time rounded to the second
tbl_b contains the real time not rounded timestamp but does not include any event data
tbl_a example:
id, time_in_sec, x, y, z, run_id
1, 95, 45, 32, 8, set_a
2, 96, 45, 33, 8, set_a
3, 97, 46, 33, 8, set_a
4, 98, 46, 34, 7, set_a
5, 99, 47, 35, 8, set_a
6, 100, 40, 32, 8, set_a
7, 101, 41, 33, 8, set_a
8, 115, 42, 32, 9, set_a
9, 116, 43, 34, 8, set_a
10, 68, 60, 50, 7, set_b
11, 69, 61, 51, 7, set_b
12, 99, 63, 53, 7, set_b
13, 100, 64, 53, 7, set_b
14, 101, 64, 52, 7, set_b
15, 102, 65, 53, 8, set_b
16, 103, 66, 54, 7, set_b
17, 125, 62, 52, 7, set_b
18, 126, 62, 53, 8, set_b
tbl1_b example:
id, engaged_time, set_id
1, 100.123, set_a
2, 115.234, set_a
3, 95.345, set_a
4, 68.454, set_b
5, 125.657, set_b
6, 99.867, set_b
Ideal output:
100.123, 100, 40, 32, 8, 101, 41, 33, 8, set_a
115.234, 115, 42, 32, 9, 116, 43, 34, 8, set_a
95.345, 95, 45, 32, 8, 96, 45, 33, 8, set_a
68.454, 68, 60, 50, 7, 69, 61, 51, 7, set_b
125.657, 125, 62, 52, 7, 126, 62, 53, 8, set_b
99.867, 99, 63, 53, 7, 100, 64, 53, 7, set_b
Thank you in advance. If more clarification is required please let me know.
Start Free Trial
