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.
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] &gt;= '20140307' AND [timestamp] &lt; '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.