SQL Server 2012: GROUP BY seconds, minutes, hours

Question

I am trying to group records in SQL Server 2012 by DateTime. I found an example on stackoverflow that does partially what I want, but the problem is that it does not group correct when it exceeds the range. If, for example, I group on minutes in blocks of 30 minutes the result is correct (see query and result below). But if I group on blocks of 120 minutes, I got the exact same result. It keeps grouping at its maximum of 60 minutes in an hour (result below). The problem is that the grouping can not take it's parent DateTime element (seconds to minutes, minutes to hours, ... , even seconds to hours,...). It is kinda logic cause I only check at minutes, but I would like to see it pass hours also. Maybe with a DATEADD(), but I don't manage to get it working.

Any ideas??

A (small) example to show what I mean:

Query:

DECLARE @TimeInterval int, @StartTime DateTime, @EndTime Datetime
SET @TimeInterval = 30
SET @StartTime='2015-01-01T08:00:00Z'
SET @EndTime = '2015-01-05T10:00:00Z'

declare @T table
(
  Value datetime
);

insert into @T values ('2015-01-01T08:00:00');
insert into @T values ('2015-01-01T08:03:00');
insert into @T values ('2015-01-01T08:06:00');
insert into @T values ('2015-01-01T08:14:00');
insert into @T values ('2015-01-01T09:06:00');
insert into @T values ('2015-01-01T09:07:00');
insert into @T values ('2015-01-01T09:08:00');
insert into @T values ('2015-01-01T11:09:00');
insert into @T values ('2015-01-01T12:10:00');
insert into @T values ('2015-01-01T13:11:00');
insert into @T values ('2015-01-02T08:08:00');
insert into @T values ('2015-01-02T08:09:00');
insert into @T values ('2015-01-03T08:10:00');
insert into @T values ('2015-01-04T08:11:00');

SELECT DATEADD(MINUTE, @TimeInterval, Convert(Datetime,CONCAT(DATEPART(YEAR, Value),'-', DATEPART(MONTH, Value),
       '-', DATEPART(DAY, Value),' ', DATEPART(HOUR, Value),':', ((DATEPART(MINUTE, Value) / @TimeInterval) * @TimeInterval),':00'),120)) as Period, 
       ISNULL(COUNT(*), 0) AS NumberOfVisitors
FROM @T
WHERE Value >= @StartTime AND Value < @EndTime
GROUP BY Convert(Datetime,CONCAT(DATEPART(YEAR, Value),'-', DATEPART(MONTH, Value), '-', DATEPART(DAY, Value),' ',
         DATEPART(HOUR, Value),':',((DATEPART(MINUTE, Value) / @TimeInterval) * @TimeInterval),':00'),120)
ORDER BY Period 

Result for 30 min
2015-01-01 08:30:00.000 | 4
2015-01-01 09:30:00.000 | 3
2015-01-01 11:30:00.000 | 1
2015-01-01 12:30:00.000 | 1
2015-01-01 13:30:00.000 | 1
2015-01-02 08:30:00.000 | 2
2015-01-03 08:30:00.000 | 1
2015-01-04 08:30:00.000 | 1

Result for 60 min
2015-01-01 08:30:00.000 | 4
2015-01-01 09:30:00.000 | 3
2015-01-01 11:30:00.000 | 1
2015-01-01 12:30:00.000 | 1
2015-01-01 13:30:00.000 | 1
2015-01-02 08:30:00.000 | 2
2015-01-03 08:30:00.000 | 1
2015-01-04 08:30:00.000 | 1

Thanks in advance!

Answer 1

You don't want datepart() for this purpose. You want a minutes count. One way is to use datediff():

SELECT datediff(minute, @StartTime, value) / @TimeInterval as minutes,
       COUNT(*) AS NumberOfVisitors
FROM @T
WHERE Value >= @StartTime AND Value < @EndTime
GROUP BY datediff(minute, @StartTime, value) / @TimeInterval
ORDER BY minutes ;

SQL Server does integer division, so you don't have to worry about remainders in this case. Also, COUNT(*) cannot return NULL, so neither ISNULL() nor COALESCE() is appropriate.

Or, to get a date/time value:

SELECT dateadd(day,
              datediff(minute, @StartTime, value) / @TimeInterval,
              @StartTime) as period,
       COUNT(*) AS NumberOfVisitors
FROM @T
WHERE Value >= @StartTime AND Value < @EndTime
GROUP BY datediff(minute, @StartTime, value) / @TimeInterval
ORDER BY period ;