Count Rows per Hour

Here is a useful technique to know.  We have a delivery service and we want to know what hour of the day is most popular for completed deliveries.  Using the DATEPART function, this can be easily calculated.

The DATEPART function “returns an integer that represents the specified datepart of the specified date.”  You can read more about DATEPART here:  https://msdn.microsoft.com/en-us/library/ms174420.aspx

In our Sales.Invoices table in the WideWorldImporters database, there is a ConfirmedDeliveryTime column which documents when a delivery was made.  We will use this field and extract the Hour part and count how many times it occurs.

For our example, we want to know during the timeframe of September 1–September 30, what times were the most deliveries made.  We can then use these numbers to adjust staffing needs.

USE [WideWorldImporters]
GO

SELECT datepart(hour, ConfirmedDeliveryTime) AS Hour_of_Day
 ,count(*) AS Count_Of_Deliveries
FROM Sales.Invoices
WHERE ConfirmedDeliveryTime >= '2015-9-01 00:00:00.0000000'
 AND ConfirmedDeliveryTime <= '2015-9-30 23:59:59.9999999'
GROUP BY datepart(hour, ConfirmedDeliveryTime)
ORDER BY 1
GO

It is always a good idea to look back at the raw data while you construct a query to make sure you query is pulling the correct information.  Below I sorted the delivery times by their hour, and you can see that only 8 deliveries were made during the 16:00–16:59 time frame.  This matches the count right above.

USE [WideWorldImporters]
GO

SELECT ConfirmedDeliveryTime
 ,datepart(hour,ConfirmedDeliveryTime)
FROM Sales.Invoices
WHERE ConfirmedDeliveryTime >= '2015-9-01 00:00:00.0000000'
 AND ConfirmedDeliveryTime <= '2015-9-30 23:59:59.9999999'

ORDER BY datepart(hour,ConfirmedDeliveryTime)
GO

The DATEPART function is very handy and can be used in all kinds of solutions.  Looking through Books Online is a very good use of time and so much can be learned from taking a peek of all of the items that are available.

https://msdn.microsoft.com/en-us/library/ms130214.aspx