Grouping by 15 minute periods

[Sanguineus]

We have a table that records a timestamp as different events take place, I am trying to group these into 15 minute buckets taking data from the last rolling calendar month.



I think I'm 99% of the way there, but I'm stumped/confused on one last element. The idea being that we can then see where our busy periods of the day are.

SELECT DATEADD("n", CP.FifteenMinutePeriod * 15, CDATE("08:00:00")) as Period, CP.FullName, SUM(CP.Calls)
FROM       
       (SELECT P.FifteenMinutePeriod, P.FullName, COUNT(T.FullName) as Calls
       FROM
               (SELECT 
               DATEDIFF("n",CDATE("08:00:00"),TimeValue(T.StartTime)) / 15 as FifteenMinutePeriod, T.FullName
               FROM LifeCycleData as T
       WHERE T.EventType = 'In Queue' AND T.StartTime >= DATEADD("m",-1,DATE())) as P
       GROUP BY P.FifteenMinutePeriod, T.FullName) as CP
GROUP BY DATEADD("n", CP.FifteenMinutePeriod * 15, CDATE("08:00:00")), FullName

This code doesn't actually seem to group by 15 minutes, I've tried different values instead of 15 but it doesn't appear to make a difference, and I'm sure I've gone code blind now having spent all of yesterday and today starting at this, playing around and tinkering.

[ranman256]

make a table tTimeBlocks, and join to either a date table to create all date/times.
then you can as for the range
between StartDateTime and EndDateTime.

startTime	endTime
0:00:00	12:14:59 AM
12:15:00 AM	12:29:59 AM
12:30:00 AM	12:44:59 AM
12:45:00 AM	12:59:59 AM
1:00:00 AM	1:14:59 AM
1:15:00 AM	1:29:59 AM
1:30:00 AM	1:44:59 AM
1:45:00 AM	1:59:59 AM
2:00:00 AM	2:14:59 AM
2:15:00 AM	2:29:59 AM
2:30:00 AM	2:44:59 AM
2:45:00 AM	2:59:59 AM
3:00:00 AM	3:14:59 AM
3:15:00 AM	3:29:59 AM
3:30:00 AM	3:44:59 AM
3:45:00 AM	3:59:59 AM
4:00:00 AM	4:14:59 AM
4:15:00 AM	4:29:59 AM
4:30:00 AM	4:44:59 AM
4:45:00 AM	4:59:59 AM
5:00:00 AM	5:14:59 AM
5:15:00 AM	5:29:59 AM
5:30:00 AM	5:44:59 AM
5:45:00 AM	5:59:59 AM
6:00:00 AM	6:14:59 AM
6:15:00 AM	6:29:59 AM
6:30:00 AM	6:44:59 AM
6:45:00 AM	6:59:59 AM
7:00:00 AM	7:14:59 AM
7:15:00 AM	7:29:59 AM
7:30:00 AM	7:44:59 AM
7:45:00 AM	7:59:59 AM
8:00:00 AM	8:14:59 AM
8:15:00 AM	8:29:59 AM
8:30:00 AM	8:44:59 AM
8:45:00 AM	8:59:59 AM
9:00:00 AM	9:14:59 AM
9:15:00 AM	9:29:59 AM
9:30:00 AM	9:44:59 AM
9:45:00 AM	9:59:59 AM
10:00:00 AM	10:14:59 AM
10:15:00 AM	10:29:59 AM
10:30:00 AM	10:44:59 AM
10:45:00 AM	10:59:59 AM
11:00:00 AM	11:14:59 AM
11:15:00 AM	11:29:59 AM
11:30:00 AM	11:44:59 AM
11:45:00 AM	11:59:59 AM
12:00:00 PM	12:14:59 PM
12:15:00 PM	12:29:59 PM
12:30:00 PM	12:44:59 PM
12:45:00 PM	12:59:59 PM
1:00:00 PM	1:14:59 PM
1:15:00 PM	1:29:59 PM
1:30:00 PM	1:44:59 PM
1:45:00 PM	1:59:59 PM
2:00:00 PM	2:14:59 PM
2:15:00 PM	2:29:59 PM
2:30:00 PM	2:44:59 PM
2:45:00 PM	2:59:59 PM
3:00:00 PM	3:14:59 PM
3:15:00 PM	3:29:59 PM
3:30:00 PM	3:44:59 PM
3:45:00 PM	3:59:59 PM
4:00:00 PM	4:14:59 PM
4:15:00 PM	4:29:59 PM
4:30:00 PM	4:44:59 PM
4:45:00 PM	4:59:59 PM
5:00:00 PM	5:14:59 PM
5:15:00 PM	5:29:59 PM
5:30:00 PM	5:44:59 PM
5:45:00 PM	5:59:59 PM
6:00:00 PM	6:14:59 PM
6:15:00 PM	6:29:59 PM
6:30:00 PM	6:44:59 PM
6:45:00 PM	6:59:59 PM
7:00:00 PM	7:14:59 PM
7:15:00 PM	7:29:59 PM
7:30:00 PM	7:44:59 PM
7:45:00 PM	7:59:59 PM
8:00:00 PM	8:14:59 PM
8:15:00 PM	8:29:59 PM
8:30:00 PM	8:44:59 PM
8:45:00 PM	8:59:59 PM
9:00:00 PM	9:14:59 PM
9:15:00 PM	9:29:59 PM
9:30:00 PM	9:44:59 PM
9:45:00 PM	9:59:59 PM
10:00:00 PM	10:14:59 PM
10:15:00 PM	10:29:59 PM
10:30:00 PM	10:44:59 PM
10:45:00 PM	10:59:59 PM
11:00:00 PM	11:14:59 PM
11:15:00 PM	11:29:59 PM
11:30:00 PM	11:44:59 PM
11:45:00 PM	11:59:59 PM

[Minty]

You can calculate it in an expression as follows;

Period15: Int(Int(Int((DatePart("h",(DateAdd("n",-480,TimeValue([YourTimeDateField]))))*60))+DatePart("n",TimeValue([YourTimeDateField])))/15)

Do this as the first stage - no grouping etc .
Once you have this the rest gets easy. (easier )

Adjust the -480 if you need a different start time. It throws a wobble if the time is before 8:00.
Edit - Add 1 if you want the count to start at 1!

[Sanguineus]

Thank you both for your suggestions, I managed to solve it with this code instead, but I will definitely take a look at both of these as I'm sure they're a lot more elegant than this.

SELECT
CDATE(FORMAT(TIMEVALUE(T.StartTime),'Short Date')) + (CDATE(FORMAT(TimeValue(T.StartTime),'Short Time')) * 1440\15)*15/1440 AS TimePeriod, FullName, Count(FullName) as TotalCalls, SUM(Duration) as TotalTimeWaiting, CDATE((SUM(Duration)/Count(FullName))) as AvgWait
FROM LifeCycleData as T
WHERE T.StartTime >= DATEADD("m",-1,Date()) AND EventType = 'In Queue'
GROUP BY CDATE(FORMAT(TIMEVALUE(T.StartTime),'Short Date')) + (CDATE(FORMAT(TimeValue(T.StartTime),'Short Time')) * 1440\15)*15/1440, FullName

[CJ_London]

the other thing to investigate using is the partition function - see this link

https://support.office.com/en-us/art...7-c94278274dc5

[Sanguineus]

the other thing to investigate using is the partition function - see this link

https://support.office.com/en-us/art...7-c94278274dc5

Many thanks for the reply Ajax, this looks far more graceful than my approach I'm definitely going to be playing with this...