LabVIEW
LabVIEW SQL Get latest data from tables
Problem:
Say I have N buildings where each building:
- Has one Humidity sensor
- Consists of M=(between 1 and 5) Sectors
- Each Sector has 1 Temperature sensor
Right now each building periodically logs data into three tables in a SQL server
Table 1: Contains 1 entry for each building ID, Name will never change, CalDate is updated periodically.
Building_ID Building_Name Building_CalDate
[I32] [String] [Date/Time]
123 Main Office 1/9/2018 4:14:32 AM
456 Security 1/8/2018 5:16:15 AM
Table 2: Contains Humidity data, each new data point is appended to the end of the list
Humidity_Date Building_ID Humidity
[Date/Time] [I32] [DBL]
1/9/2018 4:14:32 AM 123 15.23
1/9/2018 4:15:12 AM 456 20.28
1/9/2018 4:15:32 AM 123 15.50
Table 3: Contains Temperature data with Sector log, each new data point is appended to the end of the list
Temp_Date Building_ID Sector_ID Temperature
[Date/Time] [I32] [I32] [DBL]
1/9/2018 4:14:31 AM 456 0 20.23
1/9/2018 4:15:14 AM 123 1 35.23
1/9/2018 4:16:21 AM 123 0 15.23
1/9/2018 4:15:45 AM 123 2 25.23
1/9/2018 4:16:21 AM 456 0 25.23
1/9/2018 4:16:59 AM 123 1 35.23
I want to write a vi where the user can view this data in two formats as 2D Array with the latest measurement info like so:Desired Result
Right now, I've got this as my Vi that displays all data on each table,
I understand from other forums that I need to put something equivalent to
select t.Building_ID, t.Humidity_Date, t.Humidity
from Humidity t
inner join (
select username, max(Humidity_Date) as MaxHumidity_Date
from MyTable
group by Building_ID
) tm on t.Building_ID = tm.Building_ID and t.Humidity_Date = tm.MaxHumidity_Date
for each table, but I'm not sure how to implement that in LabVIEW
