get max value occurred within every 10 minute of a day


I have a table named pointdb containing columns:

SOLAR_ACTIVE_POWER_G_VAL0
LOAD_ACTIVE_POWER_G_VAL0
timestamp

It contains every second wise data. I wanted to display max of SOLAR_ACTIVE_POWER_G_VAL0 and max of LOAD_ACTIVE_POWER_G_VAL0 occurring within every 10 minute interval. For that I have written a query as follows:

Collapse | Copy Code

SELECT max(SOLAR_ACTIVE_POWER_G_VAL0) AS Solar,max(LOAD_ACTIVE_POWER_G_VAL0) AS Load, (DATEPART(MINUTE, [timestamp]) % 10) AS [Minutes]
FROM pointdb WHERE CONVERT(varchar,[timestamp],101) in('03/07/2014')
GROUP BY (DATEPART(MINUTE, [timestamp]) % 10)

But it is not giving right output.

One Response to “get max value occurred within every 10 minute of a day”

  1. ypercube says:

    What about:

    SELECT max(SOLAR_ACTIVE_POWER_G_VAL0) AS Solar,
           max(LOAD_ACTIVE_POWER_G_VAL0) AS Load, 
           DATEADD(minute, DATEDIFF(minute, 0, timestamp)/10*10, 0) AS [Minutes] 
    FROM pointdb 
    

    WHERE CONVERT(varchar,[timestamp],101) in('03/07/2014') 
    

    WHERE [timestamp] >= '20140307' AND [timestamp] < '20140308'
    GROUP BY DATEADD(minute, DATEDIFF(minute, 0, timestamp)/10*10, 0)
    

    The part

    DATEADD(minute, DATEDIFF(minute, 0, timestamp)/10*10, 0)
    

    should round down to the start of the 10 minute period.
    If you leave out /10*10, you get it to the nearest 1 minute period.

Leave a Reply

Question and Answer is proudly powered by WordPress.
Theme "The Fundamentals of Graphic Design" by Arjuna
Icons by FamFamFam