I have environmental sensors collecting some raw data from environmental stations. In addition to the sensors, there is a physical gauge measuring water height (staff gauge).
I'm trying to set up the staff gauge calculation, so that it will take the most recent offset reading from Table 2 and apply it to any new measurements coming in, without changing the previously calculated values.
station_time Air_temp_celcius Rainfall_mm pressure_mH20 water_temp_celcius sensor_depth_mH20 staff_gauge_height_m (calculated, sensor_depth_mH20 + Offset from Table2)
Observed_time Offset_m comments
My thinking was to have a function to pull the offset
SELECT TOP 1(Offset) FROM Table2 ORDER BY Observed_time DESC
Is there a way to have it only update new values coming into Table 1 without updating previous values when a new offset is entered? New offsets are entered into Table 2 very sporadically as field crews visit the site.
Additional information from comments:
There is no direct relationship between the two tables, other than the latest offset is used to calculate the staff gauge value. It should always use the most recent value.
I have specific stations and specific station offset tables. I generalized the table structure: table1_stream1 and table2_stream1_offset. As for the offset storage on the table, redundancy. Always was taught you normalize as much as possible and don't duplicate data in multiple tables if you can avoid it. I no longer work on coding and databases as much as I'd like, but some stuff sticks.
Are you saying if you have a station called "Antartica" and a station called "Africa" then you have the following tables: Antartica_stream1, Antartica_stream1_offset, Africa_stream1, and Africa_stream1_offset, etc.?
Correct. Given stations are polling hourly and not all locations carry the exact same sensor package (or sensors are in different order). Also individual stations may go offline due to technical or environmental issues.