Dynamic grouping and dynamic intervals with constantly changing start / end times

[TheoC]

Hi all,

 

The below contains sample data. The Desired Output is basically being able to group all of the records based on the Index number.

Importantly:

 

• The groups can only be 1 hour in length.
• The groups must have a start DateTime that is in the StartDateTime_Msg column.
• Although Group 1 might have a Start DateTime of "4/01/1980 2:05:03 AM" and an end DateTime of "4/01/1980 3:05:03 AM" it does not mean that the second group automatically starts thereafter.  
• The Group Start DateTime must align to the first event that does not fit within the preceeding group's one-hour window.  For example, Index 9 is the first event that occurs outside of Group 1. As such, it will form the Start DateTime for the next Group and the End DateTime will be one hour after the Start DateTime.  The Desired Output - Group corresponds to the Index number of the first event in the respective group.
• Once an event has been allocated a group, it cannot be included in any other group.
• There are approx 400,000 records.

Any additional informaiton required, please let me know! 

Thank you in advance!

THEO 🙂

 

Index	StartDateTime_Msg	EndDateTime_Msg	Desired Output - Group
1	4/01/1980 2:05:03 AM	4/01/1980 3:05:03 AM	1
2	4/01/1980 2:05:05 AM	4/01/1980 3:05:05 AM	1
3	4/01/1980 2:05:56 AM	4/01/1980 3:05:56 AM	1
4	4/01/1980 2:08:01 AM	4/01/1980 3:08:01 AM	1
5	4/01/1980 2:15:06 AM	4/01/1980 3:15:06 AM	1
6	4/01/1980 2:19:00 AM	4/01/1980 3:19:00 AM	1
7	4/01/1980 2:20:38 AM	4/01/1980 3:20:38 AM	1
8	4/01/1980 2:31:07 AM	4/01/1980 3:31:07 AM	1
9	4/01/1980 3:20:38 AM	4/01/1980 4:20:38 AM	9
10	4/01/1980 4:00:14 AM	4/01/1980 5:00:14 AM	9
11	27/01/2021 11:52:45 AM	27/01/2021 12:52:45 PM	11
12	1/10/2021 12:24:27 AM	1/10/2021 1:24:27 AM	12
13	1/10/2021 4:39:40 AM	1/10/2021 5:39:40 AM	13
14	1/10/2021 4:45:53 AM	1/10/2021 5:45:53 AM	13
15	1/10/2021 5:16:15 AM	1/10/2021 6:16:15 AM	13
16	1/10/2021 5:20:24 AM	1/10/2021 6:20:24 AM	13
17	1/10/2021 5:56:47 AM	1/10/2021 6:56:47 AM	17
18	1/10/2021 6:17:16 AM	1/10/2021 7:17:16 AM	17
19	1/10/2021 8:10:12 PM	1/10/2021 9:10:12 PM	19
20	2/10/2021 6:51:24 AM	2/10/2021 7:51:24 AM	20
21	2/10/2021 7:59:16 AM	2/10/2021 8:59:16 AM	21
22	2/10/2021 8:15:58 AM	2/10/2021 9:15:58 AM	21
23	2/10/2021 1:14:56 PM	2/10/2021 2:14:56 PM	…
24	2/10/2021 1:18:06 PM	2/10/2021 2:18:06 PM
25	2/10/2021 4:06:22 PM	2/10/2021 5:06:22 PM
26	2/10/2021 6:25:17 PM	2/10/2021 7:25:17 PM
27	2/10/2021 7:57:36 PM	2/10/2021 8:57:36 PM
28	3/10/2021 3:17:26 PM	3/10/2021 4:17:26 PM
29	3/10/2021 5:08:38 PM	3/10/2021 6:08:38 PM
30	3/10/2021 8:21:46 PM	3/10/2021 9:21:46 PM
31	4/10/2021 4:08:19 AM	4/10/2021 5:08:19 AM
32	4/10/2021 5:49:26 AM	4/10/2021 6:49:26 AM
33	4/10/2021 3:28:01 PM	4/10/2021 4:28:01 PM
34	4/10/2021 7:43:13 PM	4/10/2021 8:43:13 PM
35	5/10/2021 7:25:21 PM	5/10/2021 8:25:21 PM