SQL Server Performance Forum – Threads Archive

Additional rows

Well, its been awhile but I have been pondering on this issue and just cant figure a way.
I have a table called "tblActualValueFloat", whithin the table there are objects with names like "UI-Area4/S043~/S043E-H" pointID 707 and "UI-Area4/S043E~/S043E-T" pointID 708. (Room Humidity and Room Temperature). I have the select statement for rows named: Date, Room, and Humidity.
SELECT tblPoint.Room, dbo.UTCToLocalDateTime(tblActualValueFloat.UTCDateTime, 53) AS DateTime, tblActualValueFloat.ActualValue AS Humidity FROM tblActualValueFloat INNER JOIN
tblPointSlice ON tblActualValueFloat.PointSliceID = tblPointSlice.PointSliceID INNER JOIN
tblPoint ON tblPointSlice.PointID = tblPoint.PointID
WHERE (tblPointSlice.IsRawData = 1) AND (tblPointSlice.PointID = 707) AND(UTCDateTime >= dbo.LocalToUTCDateTime(GetDate()-1, 53) AND UTCDateTime < dbo.LocalToUTCDateTime(Getdate(), 53))
How would I get the temperature object on a different row named Temperature when the object resides as "tblActualValueFloat.ActualValue". Any help is appriciated! the
Bugster

Look up UNION in BOL, which adds separate rows for separate information – if you must. Isn’t this just a formatting issue?

UNION states using seperate tables. The obejects reside on the same table as a value, However each value has a different property. Temperature, Humidity, and Pressure, which yes they should have formated for such. If I am understanding what is meant by formating. Keep in mind I am a beginner at this. (training was only three weeks)Luckly I am doing all of this on a spare server.
I do see from a application which does somthing simmilar that there is a DECLARE statement so I will read up on the use. Thanks the
Bugster

I guess one more way to help with the union. Can you write another query, that returns the same number of columns as above, but returns the temperature, rather than the humidity?
If you can, then you can use a union statement. If you want them on the same line, rather than one below the other, this is also possible. You can join to the value table twice and return the humidity in one column, the temperature in another. It all depends on what you want your output to look like.

yes, that is what the customer wants, Humidity in one column, Temperature in one, and Pressure in one. I have created seperate views, Here is the Humidity: Create View MorningHumidity AS SELECT tblPoint.Room AS Room, dbo.UTCToLocalDateTime(UTCDateTime,53) AS Date, tblActualValueFloat.ActualValue AS Humidity FROM tblPoint INNER JOIN tblPointSlice ON tblPoint.PointID = tblPointSlice.PointID INNER JOIN tblActualValueFloat ON tblPointSlice.PointSliceID = tblActualValueFloat.PointSliceID WHERE (tblActualValueFloat.UTCDateTime >= dbo.LocalToUTCDateTime(GETDATE() – 1, 53)) AND (tblActualValueFloat.UTCDateTime < dbo.LocalToUTCDateTime(GETDATE(), 53)) AND ((tblPoint.PointID = 733) OR (tblPoint.PointID = 707) OR (tblPoint.PointID = 713) OR (tblPoint.PointID = 714) OR (tblPoint.PointID = 715) OR (tblPoint.PointID = 716)) AND (tblPointSlice.IsRawData = 1) The pointID’s all go into a Humidity column which is good however on the next view I need the pointID’s to go in another column called Pressures. CREATE VIEW MorningPressures AS SELECT tblPoint.Room AS Room, dbo.UTCToLocalDateTime(UTCDateTime,53) AS Date, tblActualValueFloat.ActualValue AS Pressure FROM tblPoint INNER JOIN tblPointSlice ON tblPoint.PointID = tblPointSlice.PointID INNER JOIN tblActualValueFloat ON tblPointSlice.PointSliceID = tblActualValueFloat.PointSliceID WHERE (tblActualValueFloat.UTCDateTime >= dbo.LocalToUTCDateTime(GETDATE() – 1, 53)) AND (tblActualValueFloat.UTCDateTime < dbo.LocalToUTCDateTime(GETDATE(), 53)) AND ((tblPoint.PointID = 70<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> OR (tblPoint.PointID = 734) OR (tblPoint.PointID = 743) OR (tblPoint.PointID = 741) OR (tblPoint.PointID = 746) OR (tblPoint.PointID = 736)) AND (tblPointSlice.IsRawData = 1) The output I need should look like: RoomDateHumidityPressureTemp When I get the results I am going to have it scheduled to print in morning. Thank you the Bugster

you got me on the Join other table, Ill read BOL on Join, and hopefully understand it better.

I used JOIN (LEFT OUTER JOIN) and ON statements after reading more. Got the result set I needed. Thanks for making me learn this.
the
Bugster

Well, you probably know the old saying…. give a man a fish, he eats for one night
teach a man to fish and he eats every night

]]>