none
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

All replies

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

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

    • Proposed as answer by Brian TkatchEditor Wednesday, May 30, 2018 5:27 PM
    • Marked as answer by Shwegc Wednesday, May 30, 2018 5:56 PM
    • Unmarked as answer by Shwegc Friday, June 1, 2018 1:57 PM
    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