# Calculating Min and Max Values

• ### Question

• Hello All:

I'm collecting the data for every one minute and storing it in a table. Now, I've to extract the data for every hour and calculate min and max value for that hour and store it in another column/table. Could anyone please let me know how it can be done?

Thanks,

Shwetha.

Wednesday, May 30, 2018 2:30 PM

• Hi,

Just a guess but you could try something like:

SELECT DATEPART(hour, [DateField]) AS DataHour, MIN([ValueField]) AS MinValue, MAX([ValueField]) AS MaxValue

FROM TableName

GROUP BY DATEPART(hour, [DateField]);

(untested)
Hope it helps...

Wednesday, May 30, 2018 2:43 PM
• Hi,

If you want the results for different groupings within the same query, my first impression is that you might be able to use GROUPING SETS.

If you can post some sample data, we could give it a try. The syntax might look something like this:

```SELECT FORMAT([DateField],'d') AS RowDate,      DATEPART(hour, [DateField]) AS RowHour,      MIN([ValueField]) AS MinValue,      MAX([ValueField]) AS MaxValue
FROM TableName
GROUP BY GROUPING SETS(FORMAT([DateField]), DATEPART(hour, [DateField]));```

(untested)

Hope it helps...

Friday, June 1, 2018 3:48 PM

### All replies

• Create a job and schedule it every hour to take MIN and MAX values to push into another table.

Wednesday, May 30, 2018 2:42 PM
• Hi,

Just a guess but you could try something like:

SELECT DATEPART(hour, [DateField]) AS DataHour, MIN([ValueField]) AS MinValue, MAX([ValueField]) AS MaxValue

FROM TableName

GROUP BY DATEPART(hour, [DateField]);

(untested)
Hope it helps...

Wednesday, May 30, 2018 2:43 PM
• Can you attach sample data. which can help us to find the solution.
Wednesday, May 30, 2018 2:58 PM
• Thank you!. It worked.

Shwetha.

Wednesday, May 30, 2018 5:56 PM
• Thank you!. It worked.

Shwetha.

Hi,

You're welcome. We're all happy to assist. Good luck with your project.

Wednesday, May 30, 2018 8:42 PM
• Hello:

One more question. How can use the query above to calculate the Min, Max values for the range of dates selected. For example, If I select the date range 05/30/2018-06/01/2018: I would like to calculate Min and Max values for1) Each day between the range 2) Every hour for each day.

Thank you very much in advance,

Shwetha.

Friday, June 1, 2018 2:02 PM
• Hi,

If you want the results for different groupings within the same query, my first impression is that you might be able to use GROUPING SETS.

If you can post some sample data, we could give it a try. The syntax might look something like this:

```SELECT FORMAT([DateField],'d') AS RowDate,      DATEPART(hour, [DateField]) AS RowHour,      MIN([ValueField]) AS MinValue,      MAX([ValueField]) AS MaxValue
FROM TableName
GROUP BY GROUPING SETS(FORMAT([DateField]), DATEPART(hour, [DateField]));```

(untested)

Hope it helps...

Friday, June 1, 2018 3:48 PM