Data historian queries

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

---

First two queries can be handled as one. Something like:

SELECT TagName, MAX(Val) AS MaxVal, MIN(Val) AS MinVal
FROM   dbo.All_Data_From_Last_Mon
GROUP BY TagName
-- ORDER BY TagName  (optionally)

I now see that these queries are SQL views, used for the third query... and I can see why this would be slow ;-)
The following reproduces the logic, but without the views, and this should allow SQL to optimize quite a bit. At any rate it provides more clarity as to what is being done...
Please "give it a spin".

SELECT  DISTINCT Mx.TagName, STR(Mx.Hours - Mn.Hours, 12, 2) AS Hours
FROM  dbo.All_Data_From_Last_Mon Mx
RIGHT OUTER JOIN dbo.All_Data_From_Last_Mon Mn ON Mx.TagName = Mn.TagName
  AND dbo.All_Data_From_Last_Mon  -- Cut the cross product a bit; may not be necessary
WHERE 
      NOT EXISTS (SELECT * FROM dbo.All_Data_From_Last_Mon Mx1 
                  WHERE Mx1.TagName = Mx.TagName AND Mx1.Hours > Mx.Hours)
  AND NOT EXISTS (SELECT * FROM dbo.All_Data_From_Last_Mon Mn1 
                  WHERE Mn1.TagName = Mn.TagName AND Mn1.Hours < Mx.Hours)

Notes:
- notice the DISTINCT in SELECT statement. That is to avoid dup lines in he case there would be several days that show the Maximun (or minumum) Hours value for that month.
- the extra condition on the join is aimed at avoiding a full 31 * 31 cross product, but the conditions that truly bring it to a single line (or several in case of dups) are the NON EXISTS predicates that follow. - A TagName+Hours index, if not readily present would greatly help.

==> I'd be interested in feedback on this query performance, as run with actual data.