To get number of records per minutes, means gouping on minutes in a given timeframe.

Some time while reviewing a log file, especially any error log of something like that, we need to find you that how much is error rate per minute, the following piece of code is used for retrieving information for every minute.

Let’s create an Error log table.

CREATE TABLE Errorlog (
	logid INT identity(1, 1)
	,ErrorMessage VARCHAR(100)
	,ErrorDateTime DATETIME
	)

Insert few sample records.

Insert into Errorlog (ErrorMessage,ErrorDateTime)values(‘System Error’,GETDATE())
Insert into Errorlog (ErrorMessage,ErrorDateTime)values(‘Miscellaneous Error’,GETDATE())
Insert into Errorlog (ErrorMessage,ErrorDateTime)values(‘General Error’,GETDATE())
Insert into Errorlog (ErrorMessage,ErrorDateTime)values(‘Disk Space Error’,GETDATE())
Insert into Errorlog (ErrorMessage,ErrorDateTime)values(‘IIS Error’,GETDATE())

Verify newly inserted records.

SELECT * FROM ERRORLOG

Query to retrieve per minute errors.

DECLARE @start DATETIME = '2014-05-30 16:09:51.220'
	,@end DATETIME = '2014-05-30 16:10:53.750'

SELECT datepart(Hour, ErrorDateTime) AS 'Hour'
	,datepart(MINUTE, ErrorDateTime) AS 'Minute'
	,count(logid) 'Count'
FROM Errorlog NOLOCK
WHERE ErrorDateTime > @start
	AND ErrorDateTime < @end
GROUP BY datepart(Hour, ErrorDateTime)
	,datepart(MINUTE, ErrorDateTime)
ORDER BY datepart(Hour, ErrorDateTime)
	,datepart(MINUTE, ErrorDateTime)