Update master table from live tables

, , ,
1

Hi,
i have 100 temperature sensors in my laboratory. I have grouped them by 10 sensors in each group. Each sensor has its own unique number.

I have created 10 tables in sql db and configured the sensor application to insert into the respective table.

Sensor application will insert a record whenever there is a temperature value change from sensor id and temperature value.

Sensor application don't have update table option. It will only insert records.

My request is, i want to see all 100sensors latest value with sensor id in a different table called master table, so that i can see all sensors latest value at one place. (Update recent record from each table with unique sensor)

Please share your ideas with sample sql query

2

I am not going to provide a sample - simply because I think you are going in the wrong direction. Instead of having 10 separate tables where each table has 10 different sensors - you only need a single table to hold those results.

That doesn't mean you only have one table - I would start with a table that identifies each sensor. There would be 100 rows at this time - one for each sensor. I would then have a second table that identifies each group - and a column in the first table related to this second table to identify which group the sensor belongs.

The third table is the results - the sensor data would be inserted into this table using the identifier from the first table to denote the sensor inserting the data.

With that - you can create a query (view) instead of trying to update a table and get the latest values from the results for each sensor. Several ways to approach that query:

Row Number:

WITH latestResults
AS (
SELECT SensorID, SensorDateTime, SensorValue, rn = row_number() over(partition by SensorID order by SensorDateTime DESC
FROM results
WHERE SensorDateTime >= DATEADD(minute, -15, getdate())  --only look back 15 minutes
)
SELECT ...
FROM latestResults
WHERE rn = 1;

CROSS APPLY:

SELECT s.SensorID
     , d.SensorDateTime
     , d.SensorValue
FROM Sensor    s

CROSS APPLY (SELECT TOP (1) * 
               FROM results r
              WHERE r.SensorID = s.SensorID
              ORDER BY
                    r.SensorDateTime DESC
                  )                  d

You can expand on this by adding in the group table - if needed.

3

I agree with jeff's approach -- normalizing the tables -- although I would name the tables differently:

tables / data

sensors / contains the unique sensor id, and perhaps a sensor name, date activated, etc.
that is, info ONLY about the sensor itself.
groups / contains a unique group number, perhaps a group name, etc.
that is, info ONLY about the group itseelf
sensor_groups / contains unique sensor id and unique group number that sensor is in
sensor_results / contains temperature, datetime of results, etc..

4

Thanks Jeff and Scott,

My customer is very specific to the number of Tables, we requested to create 10 different tables for the group of sensors as each group is having different columns along with temperature value and timestamp.

The tables look like below, I agree, still i can go with single table to have all sensors data, but this can't be changed as per customer requirement.

I have created a single table to have all these 100 sensors data (Only Specific Columns from each table), This may be like Reverse Engineering, From Multiple table to one table with latest information from each table to the master table.

SensorID Timestamp Value Pressure
123 30/10/2023 09:14:13 100 98
123 30/10/2023 09:14:14 101 89
198 30/10/2023 09:14:13 201 98
198 30/10/2023 09:14:15 202 89
123 30/10/2023 09:14:16 102 98
SensorID Timestamp Value PASS Value
201 30/10/2023 09:14:13 100 100
201 30/10/2023 09:14:14 101 100
298 30/10/2023 09:14:13 201 100
298 30/10/2023 09:14:15 202 100
201 30/10/2023 09:14:16 102 8912
5

Can you try this query, I hope you can get what you are looking for.

CREATE TABLE master_temperature (
    sensor_id INT NOT NULL,
    temperature FLOAT NOT NULL,
    reading_time TIMESTAMP NOT NULL
);

INSERT INTO master_temperature (sensor_id, temperature, reading_time)
SELECT sensor_id, temperature, reading_time
FROM sensor_table_1
WHERE (sensor_id, reading_time) IN (
    SELECT sensor_id, MAX(reading_time)
    FROM sensor_table_1
    GROUP BY sensor_id
)
UNION ALL
SELECT sensor_id, temperature, reading_time
FROM sensor_table_2
WHERE (sensor_id, reading_time) IN (
    SELECT sensor_id, MAX(reading_time)
    FROM sensor_table_2
    GROUP BY sensor_id
)
-- Repeat the above UNION ALL for the other 8 sensor tables (sensor_table_3 to sensor_table_10)
ORDER BY sensor_id;

Thanks