Wikis - Page Details

First published by Naomi N (eMicrosoft Partne)
When: 19 Jun 2013 12:21 PM
Last revision by Naomi N (eMicrosoft Partne)
When: 3 Sep 2013 10:55 AM
Revisions: 9
Comments: 8

Options

Can You Improve This Article?

Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.

T-SQL: Group by Time Interval

Simple Problem Definition
Solution
Complex Problem Definition and Solution
See Also

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.