{Min && Max} Query W/Timestamp?
Sep 7, 2007
Hello, I have the following queries below that are part of a daily report that I produce.
SELECT TOP 1 DateTime, TagName, CONVERT(varchar(15), DateTime, 108) AS Time, CONVERT(decimal(38, 3), Value) AS Min
FROM v_AnalogHistory
WHERE (DateTime >= CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS DATETIME)) AND (DateTime < CAST(CONVERT(VARCHAR(8), GETDATE(), 112)
AS DATETIME)) AND (TagName = N'FIR_301') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)
##Output of above query;
DateTime TagName Time Max Occured Min Value
9/6/2007 11:37:50 AM FIR_301 11:37:50 4.066
SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime >= CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS DATETIME)) AND (DateTime < CAST(CONVERT(VARCHAR(8), GETDATE(), 112)
AS DATETIME)) AND (TagName = N'FIR_301') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value) DESC, CONVERT(varchar(15), DateTime, 108) DESC
##Output of above query;
DateTime TagName Time Max Occured Max Value
9/6/2007 4:59:30 AM FIR_301 04:59:30 37.208
What I would like to do is write a query that will calculate the {Min & Max Value} and the {Time} that they occured
and would like to combine the above queries into one query.
Example:
{Some Query would produce the following output}; using the following tagnames: FIR_301,FIR_401,FIR_501,etc......
DateTime TagName Time Max Occured Min Value
9/6/2007 11:37:50 AM FIR_301 11:37:50 4.066
DateTime TagName Time Max Occured Max Value
9/6/2007 4:59:30 AM FIR_301 04:59:30 37.208
DateTime TagName Time Max Occured Min Value
9/6/2007 11:37:50 AM FIR_401 11:22:16 2.056
DateTime TagName Time Max Occured Max Value
9/6/2007 4:59:30 AM FIR_401 02:59:30 17.208
DateTime TagName Time Max Occured Min Value
9/6/2007 11:37:50 AM FIR_501 14:23:26 7.059
DateTime TagName Time Max Occured Max Value
9/6/2007 4:59:30 AM FIR_501 22:59:30 7.248
Thank You for any help provided.