T-SQL: Group by Time Interval

T-SQL: Group by Time Interval



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.
Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Revision 5. Comment: Added See Also

  • Naomi  N edited Revision 4. Comment: Past tense

  • Naomi  N edited Revision 3. Comment: Attempt to fix code blocks formatting

  • Richard Mueller edited Revision 2. Comment: Spelling

  • Naomi  N edited Revision 1. Comment: Added toc

  • Naomi  N edited Original. Comment: Minor grammar corrections

Page 1 of 1 (6 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Naomi  N edited Original. Comment: Minor grammar corrections

  • Naomi  N edited Revision 1. Comment: Added toc

  • Richard Mueller edited Revision 2. Comment: Spelling

  • Strange, the code blocks now look bad

  • Naomi  N edited Revision 3. Comment: Attempt to fix code blocks formatting

  • Naomi  N edited Revision 4. Comment: Past tense

  • Naomi  N edited Revision 5. Comment: Added See Also

  • Hi,

    For Quarter Hours, that should work too

    SELECT  DATEPART(minute, JobComplete)/15 AS [QuaterHour],

           COUNT(JobId) AS [Jobs Completed]

    FROM    dbo.Jobs

    WHERE   JobComplete BETWEEN @StartTime AND @EndTime

    GROUP BY DATEPART(minute, JobComplete)/15

Page 1 of 1 (8 items)