Data historian queries
- by Scott Dennis
Hi, I have a table that contains data for electric motors the format is:
DATE(DateTime) | TagName(VarChar(50) | Val(Float) |
2009-11-03 17:44:13.000 | Motor_1 | 123.45
2009-11-04 17:44:13.000 | Motor_1 | 124.45
2009-11-05 17:44:13.000 | Motor_1 | 125.45
2009-11-03 17:44:13.000 | Motor_2 | 223.45
2009-11-04 17:44:13.000 | Motor_2 | 224.45
Data for each motor is inserted daily, so there would be 31 Motor_1s and 31
Motor_2s etc. We do this so we can trend it on our control system displays.
I am using views to extract last months max val and last months min val.
Same for this months data. Then I join the two and calculate the difference
to get the actual run hours for that month. The "Val" is a nonresetable
Accumulation from a PLC(Controller). This is my query for Last months Max
Value:
SELECT TagName, Val AS Hours
FROM dbo.All_Data_From_Last_Mon AS cur
WHERE (NOT EXISTS
(SELECT TagName, Val
FROM dbo.All_Data_From_Last_Mon AS high
WHERE (TagName = cur.TagName) AND (Val > cur.Val)))
This is my query for Last months Max
Value:
SELECT TagName, Val AS Hours
FROM dbo.All_Data_From_Last_Mon AS cur
WHERE (NOT EXISTS
(SELECT TagName, Val
FROM dbo.All_Data_From_Last_Mon AS high
WHERE (TagName = cur.TagName) AND (Val < cur.Val)))
This is the query that calculates the difference and runs a bit slow:
SELECT dbo.Motors_Last_Mon_Max.TagName, STR(dbo.Motors_Last_Mon_Max.Hours - dbo.Motors_Last_Mon_Min.Hours, 12, 2) AS Hours
FROM dbo.Motors_Last_Mon_Min RIGHT OUTER JOIN
dbo.Motors_Last_Mon_Max ON dbo.Motors_Last_Mon_Min.TagName = dbo.Motors_Last_Mon_Max.TagName
I know there is a better way. Ultimately I just need last months total and this months total. Any help would be appreciated.
Thanks in advance
