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.

Answer 1

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

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

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...

Answer 3

Can you attach sample data. which can help us to find the solution.

Answer 4

Thank you!. It worked.

Shwetha.

Answer 5

Thank you!. It worked.

Shwetha.

Hi,

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

Answer 6

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.

Answer 7

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...