I need to query a monthly history table to pull the minimum and maximum values and the times they occured for a report. I know to use min() and max() functions to get the values but not sure how to get the times?
thanks for any help,
Scott
I need to query a monthly history table to pull the minimum and maximum values and the times they occured for a report. I know to use min() and max() functions to get the values but not sure how to get the times?
thanks for any help,
Scott
SELECT time_stamp FROM table WHERE value=(select MAX(value) from table) ;
Thanks for the help but i’m not sure this works for me. here is a sample of the query I’m using to populate a report. The report gives the min, max, and avg value for each day over a period of x days for given tags. I need to work the times for the min and max into the query. Ideally i would like to get two more columns for MinTime and MaxTime.
select date(FROM_UNIXTIME(t_stamp/1000)) as ‘Date’,
max(FloatValue) as Max,
min(FloatValue) as Min,
avg(FloatValue) as Avg,
tagpath as Tag
from sqlt_data_1_2013_08 as data, sqlth_te as tag
where data.tagid = tag.id and tag.tagpath = ‘Effluent/AWN_Eff_flow’ and
{Root Container.WhereClause} // This is the date range of the report
group by Date
Union
{repeat for additional tags}
Again, Thanks for the help.
Here's an example I was given (from an SQL help forum) which does what you require.
DECLARE @t TABLE
(
[ndx] [int] IDENTITY(1,1) NOT NULL,
[value] [int] NULL, [t_stamp] [datetime] NULL);
INSERT INTO @t VALUES (1,'20130823')
INSERT INTO @t VALUES (1,'20130824')
INSERT INTO @t VALUES (2,'20130825')
INSERT INTO @t VALUES (3,'20130826')
INSERT INTO @t VALUES (4,'20130827'
)INSERT INTO @t VALUES (5,'20130828')
INSERT INTO @t VALUES (5,'20130829')
;WITH
q1 (min_value, min_t_stamp) AS (
SELECT min(value), min(t_stamp)
FROM @t
WHERE value = (SELECT min(value) FROM @t)
),
q2 (min_value, min_t_stamp) AS (
SELECT min(value), min(t_stamp)
FROM @t
WHERE value = (SELECT max(value) FROM @t)
)
SELECT *
FROM q1, q2
Or this:
WITH a (aval, astamp) AS (
SELECT min(value), min(t_stamp) from @t
WHERE value = (SELECT min(value) FROM @t)
)
, b (bval, bstamp) AS (
SELECT min(value), min(t_stamp) from @t
WHERE value = (SELECT max(value) FROM @t)
)
SELECT
a.aval
, a.astamp
, b.bval
, b.bstamp
FROM
a
CROSS JOIN b
And this does the same:
SELECT
col1 = MIN(CASE WHEN t.value = x.min_value THEN value ELSE NULL END),
col2 = MIN(CASE WHEN t.value = x.min_value THEN t_stamp ELSE NULL END),
col3 = MIN(CASE WHEN t.value = x.max_value THEN value ELSE NULL END),
col4 = MIN(CASE WHEN t.value = x.max_value THEN t_stamp ELSE NULL END)
FROM @t t
CROSS APPLY (
SELECT min_value = MIN(value), max_value = MAX(value)
FROM @t
) x
WHERE t.value IN (x.min_value, x.max_value)