Using SQL Server 2005...
Here is a simplified case of what I'm trying to do:
I have a table that contains time stamped data recorded from remote sensors. I would like to have a single stored procedure that can return some aggregate data for for a given sensor over a given time frame.
I'm wondering how I query for ONE row, and then take a few of the fields' data and put it in output parameters.
Can I get a little help with the best way to accomplish this? This would be psudo code for what I want to do...
CREATE PROCEDURE GetSensorSummary
@SensorDescription nvarchar(127) out,
@MaxValue double out,
@MaxTime datetime out,
@MinValue double out,
@MinTime datetime out
-- put the description from this query in @SensorDescription
-- which I could do like this if only getting one value (I actually want to grab more than one):
SET @SensorDescription = SELECT Description FROM SensorDef WHERE SensorID=@SensorID
-- the queries below would also contain the date criteria in the WHERE clause (left out for brevity)
SELECT TOP 1 DataValue, DataTime FROM SensorData WHERE SensorID=@SensorID ORDER BY DataValue DESC
-- want to set @MaxValue=DataValue and @MaxTime=DataTime
SELECT TOP 1 DataValue, DataTime FROM SensorData WHERE SensorID=@SensorID ORDER BY DataValue ASC
-- want to set @MinValue=DataValue and @MinTime=DataTime