Group records by every n minutes datetime range based on Min(date) in each group in sql server

Question

I have these sample records in my table, I want to group them by every 3 minutes.based on min(Matched_BeginTime) on each group.

Keywords    Matched_BeginTime        Station_ID       Program_ID    TVCapCID    ScheduleID
"Police"    2015-11-17 17:00:37.160   120           715             486290066   102691
"Police"    2015-11-17 17:01:48.620   120           715             486290088   102691
"Police"    2015-11-17 17:02:02.140   120           715             486290091   102691
"Police"    2015-11-17 17:02:53.100   120           715             486290102   102691
"Police"    2015-11-17 17:03:03.540   120           715             486290106   102691
"Police"    2015-11-17 17:03:48.020   120           715             486290119   102691
"SAS"       2015-11-17 17:03:57.940   120           715             486290122   102691
"SAS"       2015-11-17 17:08:25.800   120           715             486290197   102691
"Police"    2015-11-17 17:08:32.960   120           715             486290199   102691
"Emergency" 2015-11-17 17:09:02.540   120           715             486290206   102691
"SAS"       2015-11-17 17:10:55.640   120           715             486290246   102691
"Police"    2015-11-17 17:12:23.240   120           715             486290273   102691

this is my ideal result:

Keywords_Groups         Min_Matched_BeginTime   
Police                  11/17/2015 17:00:37     
Police,SES              11/17/2015 17:03:48      
SES,Police,Emergency    11/17/2015 17:08:26      
Police                  11/17/2015 17:12:23

Each record is grouped by matched_Date in every 3 minutes .
forexp , first 5 records should be in one group because theyre in range of min(matchDate)+ 3minutes

Note : I tried to use the solutions given before. like these ones:
How to group time by hour or by 10 minutes

SQL SERVER - Group records by n minutes interval

this is the sample code I tried from above solutions:

select datepart(minute, Matched_BeginTime)/3,min(Matched_BeginTime),Keywords
from [table]
group by datepart(minute, Matched_BeginTime)/3,Keywords
order by MIN(Matched_BeginTime)

and This is the result :

0   2015-11-17 17:00:37.160 "Police" 
1   2015-11-17 17:03:03.540 "Police" 
1   2015-11-17 17:03:57.940 "SAS" 
2   2015-11-17 17:08:25.800 "SAS" 
2   2015-11-17 17:08:32.960 "Police" 
3   2015-11-17 17:09:02.540 "Emergency" 
3   2015-11-17 17:10:55.640 "SAS" 
4   2015-11-17 17:12:23.240 "Police"

As you can see, it grouped by minutes division by 3 , started from 00 but not based on Min(Matched_BeginTime)

Thank you for any help

Note:By using Stored Procedure, I can do it fine, I prefer to have a single TSQL command for that.

How do you want to "roll-up" your Station ID's and Program ID's though? For example, could there ever be a situation where there are different ID's within a 3 minute period? If so, how should these be displayed? — Sean Cox, Nov 18 '15 at 04:25
@TheShaman No , actually they can be ignored. I've removed them from the result — Sara N, Nov 18 '15 at 04:39
You can use a sub query to get the min timestamp and use datediff to find minutes and divide by 3 as above. https://msdn.microsoft.com/en-us/library/ms189794.aspx — Hogan, Nov 18 '15 at 05:34
@Hogan thanks for the tip. I tried that, but I cant use aggregate function on group . how can I use it ? could you please write a sample about what you mean? — Sara N, Nov 19 '15 at 02:50

score 0 · Answer 1 · edited May 23 '17 at 11:51

You can use a sub query to get the min timestamp and use datediff to find minutes and divide by 3 as above. msdn.microsoft.com/en-us/library/ms189794.aspx

Sub query to get min timestamp

Select min(Matched_BeginTime) as minMBT FROM [table]

Add that into you query and use datediff:

select datediff(minute, sub.minMBT, Matched_BeginTime)/3,Keywords
from [table], (Select min(Matched_BeginTime) as minMBT FROM [table]) as Sub
group by datediff(minute, sub.minMBT, Matched_BeginTime)/3,Keywords
order by datediff(minute, sub.minMBT, Matched_BeginTime)/3

note: Adding this sub-query to the from line with no where is the same as a "cross join" or a regular join with no on clause -- since sub just has one row and one column it just adds that single value to each row.

If you want to have multiple rows "joined" with a comma you can use the technique I show here: https://stackoverflow.com/a/1785923/215752

However, if you have a front end used for display I expect creating the comma separated list there is easier.

Group records by every n minutes datetime range based on Min(date) in each group in sql server

1 Answers1