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)
```