Tuesday, 30 September 2014

SQL Server - Group DateTime by Minutes / x minutes

If you want to group by records based on Minutes use the below Group By:
GROUP BY
DATEPART(YEAR, SUB.[SubOnDate]),
DATEPART(MONTH, SUB.[SubOnDate]),
DATEPART(DAY, SUB.[SubOnDate]),
DATEPART(HOUR, SUB.[SubOnDate]),
DATEPART(MINUTE, SUB.[SubOnDate])

If you want to group by records by x minutes, it is as below. In the sample below its grouped by every 10 minutes. so records within each 10 minutes will be in the same group.
GROUP BY
DATEPART(YEAR, SUB.[SubOnDate]),
DATEPART(MONTH, SUB.[SubOnDate]),
DATEPART(DAY, SUB.[SubOnDate]),
DATEPART(HOUR, SUB.[SubOnDate]),
(DATEPART(MINUTE, SUB.[SubOnDate]) / 10)

No comments:

Post a comment