Count entries in a SQL table by minute

Imagine that you need to count entries on a table by date. That query is rather obvious and probably you already made it sometimes. And what if you need to count entries on a table by minute? For instance, to determine the period when more orders are received.

select count(*), datepart(HH, lastchange), datepart(MI,lastchange)
from ORDERSTABLE
group by datepart(HH, lastchange), datepart(MI,lastchange)
order by datepart(HH, lastchange), datepart(MI,lastchange)

Running that query two things are immediately wrong. The hours separated from minutes, the minutes with one digit, and some periods have no info and therefore no entry saying 0.


The first step is to fill the blanks with 0. For that, we'll generate a temporary table with all the hours and minutes from 0:00 until 23:59. We'll be using Common Table Expressions and a temporary table for that.
Just pick a day, and use DateAdd to increase it minute by minute. With Recursion it will fill the entire table based on that rule.

WITH mycte AS(
SELECT CAST('2011-01-01 0:00' AS DATETIME) DateValue
UNION ALL
SELECT DATEADD(MINUTE, 1, DateValue)
FROM mycte
WHERE DateValue < '2011-01-02'
)

SELECT DATEPART(HH,DateValue) hours,DATEPART(MI,DateValue) minutes
INTO #temptable
FROM mycte OPTION (MAXRECURSION 0)

Finally, we'll left join the complete table and our results, and write the time in a proper format.

SELECT REPLICATE('0',2-LEN(hours))+CAST(hours AS VARCHAR)+
':'+REPLICATE('0',2-LEN(minutes))+CAST(minutes AS VARCHAR),COUNT(lastchange)
FROM #temptable
LEFT JOIN ORDERSTABLE ON hours = DATEPART(HH, lastchange) AND minutes=DATEPART(MI,lastchange)
GROUP BY hours, minutes
ORDER BY hours, minutes

Don't forget to drop the temporary table.

DROP TABLE #temptable

And that's it.