Count aggregated data on every nth hour

Question

I have some data generated during time. I used the query below to count the number of "interactions" which happened every hour.

SELECT COUNT(*) as Quantity, FORMAT(cast(InteractionDate as datetime2), 'yyyy-MM-dd HH')  as Datum
FROM Interaction as in
INNER JOIN Mission as mi
on in.MissionID=mi.MissionID
WHERE InteractionDate between '2015-01-13 12' AND '2015-01-22 12'
GROUP BY FORMAT(cast(InteractionDate as datetime2), 'yyyy-MM-dd HH') 
ORDER BY Datum

The query above gives me this:

116 | 2015-01-15 00 
37  | 2015-01-15 01  
17  | 2015-01-15 02

Now i want to get the aggregated number of interactions on every nth hour. Let's say I want every 3rd hour, for the data provided I would get:

170 | 2015-01-15 02

How can I do that?

What do you mean by "aggregated number of interactions on every nth hour"? Sample data and desired results are always helpful. — Gordon Linoff, Jan 27 '15 at 13:31
I edited the question, hope it clarified the situation a bit — omegasbk, Jan 27 '15 at 13:36

score 0 · Answer 1 · answered Jan 27 '15 at 13:10

You could group by date and hour separately, this would let you have hour-expressions. For example;

GROUP BY cast(InteractionDate as date), (hour(InteractionDate)/4)

This would give you midnight to 6am in the first bucket, 6am to midday in the next etc.

score 0 · Answer 2 · edited May 23 '17 at 12:20

You can aggregate data by any period of time by getting the interval using datediff, and then making the integer division, like this:

group by datediff(hour, '1990-01-01T00:00:00', yourDatetime) / 3

The maths are: get the integer number of hours from the base date, and make an integer division by 3, what yields groups of 3 consecutive hours with the same result. And then it's use to group the data.

This wil get the number of hours from the base date-time that you want to specify. The only important part, in this case, is the time part, which let you decide the inital point for the 3 hour intervals. In this case, the intervals are [00:00 to 03:00], [03:00 to 06:00] and so on. If you need different intervals, make a different base date-time, for example '1990-01-01T00:01:00' would give you the intervals [01:00 to 04:00], [04:00 to 07:00], and so on.

To get further details, see this full answer: Group DateTime into 5,15,30 and 60 minute intervals

In this answer you'll see how you can show the start and end date-time of each interval, apart from the aggregated values. And have a deeper insight into this solution.

Count aggregated data on every nth hour

2 Answers2