Share via

T-SQL: Group by Time Interval

  • Article

Simple Problem Definition

A question was posted today in Transact-SQL forum "Counts by Time Interval" The thread originator wanted to know how to find how many jobs were completed in each hour in a certain interval for the current shift. The solution I implemented is based on the DATEPART function that allows to get hour part of the datetime variable (or column).

Solution

This is the solution suggested:

SELECT datepart(hour, JobComplete) as  [Hour], COUNT(JobId) as  [Jobs Completed]
  
FROM dbo.Jobs
  
WHERE  JobComplete between @StartTime and @EndTime
  
GROUP BY  datepart(hour, JobComplete)

This solution assumes, that @StartTime and @EndTime variables will be set for the current day interval (otherwise we may want to add CAST(JobComplete AS DATE) into select list and GROUP BY list.

Complex Problem Definition and Solution

Now, this is a very straightforward problem. What if we need to solve slightly more complex problem of grouping by every 15 (Nth) minutes? I discussed this problem before as a first problem in this blog post "Interesting T-SQL problems". Below is a solution from that blog post:

;With cte As
(Select DateAdd(minute, 15 * (DateDiff(minute, '20000101', SalesDateTime) / 15), '20000101')  As  SalesDateTime, 
SalesAmount
From @Sales)
Select SalesDateTime, Cast(Avg(SalesAmount) As  decimal(12,2)) As AvgSalesAmount
From cte
Group By  SalesDateTime;

Finally, a few notes on missing data possibility. If we want to display data for all times in the predefined interval, even if we don't have data for particular hour, for example, we need to have a Calendar table analogue first and LEFT JOIN from that table of all needed time intervals to our summary solution.

See Also

This entry participated in the TechNet Guru contributions for June contest.