T-SQL Group by n-minutes

There are literally hundreds if not thousands of articles on the internet explaining how to group by n-minutes, most of them utilizes this technique:

DATEADD(MINUTE, @interval *(DATEDIFF(MINUTE, '20000101', sampletime) / @interval), '20000101')  

Above will round sampletime to nearest 10 minutes interval which is desired in some cases, but if you e.g. use aggregate functions, your result will only be correct each n’th minute. Therefore I have created this little query which do not round, and is excellent for graphing time sensitive data.

DECLARE @starttime DATETIME = GETDATE() -- Start grouping from here
DECLARE
@lookback INT = 120 -- Group backwards 120 minutes from start
DECLARE
@interval INT = 20 -- Group in intervals of 20 minutes

SELECT
(t.sampleinterval * @interval) AS [+ Min], AVG(querydc) AS [QueryDC]
FROM
(SELECT FLOOR(DATEDIFF(MI, sampletime, @starttime) / @interval) AS sampleinterval, querydc
       FROM dbo.mylogtable
       WHERE (sampletime >= DATEADD(MI, -(@lookback-1), @starttime)) AND(sampletime <= @starttime)) AS t

GROUP
by t.sampleinterval
ORDER
by t.sampleinterval

Below  is the output it generates, if  @starttime is e.g. 12:13 then the first row will show the average value for QueryDC in the interval between 12:13 and 11:53, and the second row in the interval between 11:53 and 11:33 etc. etc.

+ Min     QueryDC
-----------------
0         64
20        70
40        69
60        90
80        102
100       157

Localization on Reports in Reporting Services

I came across a report in reporting services that had a lot of dates in the data.
When the report eventually ended up un the report server I had a most annoying problem.
My dates was displayed in the wrong format.
I needed them to show up in danish and they showed up in english.
After a heavy doze of trial and error I finally got it.
On every report there is a property called language.
A lot of peers told me that this setting did not matter if it was not set in the first place.
Guys - it does matter.

If you want your report to show up in an different locale then set the language to the appropriate countrys locale.
If you want to support multiple languages, then set an expression on the language instead where you set the value to user.language
After this it is the language settings in Internet Explorer that decides the locale settings in the report.

Best regards
Michael Torpegart

Month List

RecentPosts