Resources For IT Professionals

# 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.

This entry participated in the TechNet Guru contributions for June contest.
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful

• 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.
• 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

• 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)