Trying to identify groupings in events

Question

Post reply

Trying to identify groupings in events

tacy.highland

Hall of Fame

Points: 3319

April 3, 2022 at 9:42 pm

#4010176

Hello,

I have a transportation dataset with pickups and drop offs, which are on routes. Each pick up with corresponding drop off has a unique ID (BookingID). The sample data has all the trips on three routes. I need to identify groups of 3 or more picks or drops which are either picking up at or dropping off at the same address. I have a column that does this ("GRP"), but the issue is when there is a group within a group. For example, if a route picks up 3 people at the same address, then picks up others, then they all go to the same drop address, that should be considered one group because all the people are all dropping at the same address. Right now it's flagging both the pickup with the 3 riders as one group and all the drops as the second group (See route 810030 as Grp 10 and 16). Below is the sample data:

CREATE TABLE [dbo].[TABLETESTDATA](
[RN] [int] NULL,
[LDate] [int] NULL,
[EvstrName] [varchar](25) NULL,
[ClientID] [int] NULL,
[BookingID] [int] NULL,
[Address] [varchar](254) NULL,
[PickDropHour] [int] NULL,
[Event_Activity] [varchar](4) NULL,
[EstTime] [varchar](5) NULL,
[EstTimeSec] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (1, 20200925, N'810030', 109282, 31081478, N'470 N WALNUT ST LA HABRA', 4, N'Pick', N'04:09', 14970)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (2, 20200925, N'810030', 109282, 31081478, N'595 TAMARACK AVE BREA', 4, N'Drop', N'04:20', 15630)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (3, 20200925, N'810030', 54570, 31102067, N'315 W LINCOLN AVE ORANGE', 4, N'Pick', N'04:55', 17700)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (4, 20200925, N'810030', 54570, 31102067, N'2684 N TUSTIN ST ORANGE', 4, N'Drop', N'04:59', 17970)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (5, 20200925, N'810030', 63480, 31102123, N'520 N BREA BLVD BREA', 5, N'Pick', N'05:34', 20040)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (6, 20200925, N'810030', 63480, 31102123, N'410 N FAIRVIEW ST SANTA ANA', 5, N'Drop', N'05:54', 21270)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (7, 20200925, N'810030', 140108, 31082123, N'1140 W CUBBON ST SANTA ANA', 6, N'Pick', N'06:32', 23550)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (8, 20200925, N'810030', 140108, 31082123, N'17197 NEWHOPE ST FOUNTAIN VALLEY', 6, N'Drop', N'06:47', 24450)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (9, 20200925, N'810030', 100593, 31081767, N'15411 VERMONT ST WESTMINSTER', 7, N'Pick', N'07:23', 26580)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (10, 20200925, N'810030', 114497, 31081576, N'8791 MONTEREY CIR WESTMINSTER', 7, N'Pick', N'07:32', 27150)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (11, 20200925, N'810030', 137816, 31081566, N'9791 CORNWALL AVE WESTMINSTER', 7, N'Pick', N'07:45', 27900)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (12, 20200925, N'810030', 139972, 31081601, N'9791 CORNWALL AVE WESTMINSTER', 7, N'Pick', N'07:45', 27901)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (13, 20200925, N'810030', 67676, 31081414, N'9791 CORNWALL AVE WESTMINSTER', 7, N'Pick', N'07:45', 27930)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (14, 20200925, N'810030', 129805, 31081921, N'9791 CORNWALL AVE WESTMINSTER', 7, N'Pick', N'07:45', 27931)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (15, 20200925, N'810030', 112188, 31081855, N'16098 BANANA ST FOUNTAIN VALLEY', 7, N'Pick', N'07:55', 28500)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (16, 20200925, N'810030', 143945, 31081662, N'15111 BUSHARD ST WESTMINSTER', 8, N'Pick', N'08:01', 28890)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (17, 20200925, N'810030', 112188, 31081855, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:19', 29940)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (18, 20200925, N'810030', 129805, 31081921, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:19', 29941)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (19, 20200925, N'810030', 67676, 31081414, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:19', 29942)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (20, 20200925, N'810030', 100593, 31081767, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:19', 29943)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (21, 20200925, N'810030', 114497, 31081576, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:19', 29944)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (22, 20200925, N'810030', 139972, 31081601, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:19', 29945)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (23, 20200925, N'810030', 137816, 31081566, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:19', 29946)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (24, 20200925, N'810030', 143945, 31081662, N'17150 EUCLID ST FOUNTAIN VALLEY', 8, N'Drop', N'08:26', 30360)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (25, 20200925, N'810030', 11731, 31100557, N'2553 ANACAPA DR COSTA MESA', 10, N'Pick', N'10:17', 37050)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (26, 20200925, N'810030', 93149, 31101174, N'11180 WARNER AVE FOUNTAIN VALLEY', 10, N'Pick', N'10:32', 37950)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (27, 20200925, N'810030', 93149, 31101174, N'2117 W MYRTLE ST SANTA ANA', 10, N'Drop', N'10:48', 38880)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (28, 20200925, N'810030', 11731, 31100557, N'101 THE CITY DR ORANGE', 11, N'Drop', N'11:07', 40050)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (29, 20200925, N'810030', 118744, 31100729, N'790 THE CITY DR S ORANGE', 11, N'Pick', N'11:23', 41010)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (30, 20200925, N'810030', 118744, 31100729, N'224 E STANFORD ST SANTA ANA', 11, N'Drop', N'11:42', 42120)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (31, 20200925, N'810030', 91990, 31082233, N'295 E YALE LOOP IRVINE', 13, N'Pick', N'13:39', 49140)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (32, 20200925, N'810030', 145875, 31081777, N'295 E YALE LOOP IRVINE', 13, N'Pick', N'13:39', 49141)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (33, 20200925, N'810030', 43621, 31081555, N'295 E YALE LOOP IRVINE', 13, N'Pick', N'13:39', 49142)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (34, 20200925, N'810030', 72627, 31082475, N'295 E YALE LOOP IRVINE', 13, N'Pick', N'13:39', 49143)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (35, 20200925, N'810030', 145875, 31081777, N'14741 HOLT AVE TUSTIN', 14, N'Drop', N'14:03', 50580)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (36, 20200925, N'810030', 91990, 31082233, N'400 S SUNKIST ST ANAHEIM', 14, N'Drop', N'14:27', 52050)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (37, 20200925, N'810030', 43621, 31081555, N'400 S SUNKIST ST ANAHEIM', 14, N'Drop', N'14:28', 52110)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (38, 20200925, N'810030', 72627, 31082475, N'1166 N CITRON ST ANAHEIM', 14, N'Drop', N'14:43', 53010)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (1, 20200925, N'811930', 107443, 31093387, N'1342 W ELM AVE FULLERTON', 7, N'Pick', N'07:15', 26130)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (2, 20200925, N'811930', 80064, 31082042, N'117 EDWARD AVE FULLERTON', 7, N'Pick', N'07:32', 27150)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (3, 20200925, N'811930', 143114, 31082288, N'400 W ORANGETHORPE AVE FULLERTON', 7, N'Pick', N'07:49', 28170)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (4, 20200925, N'811930', 124521, 31081979, N'1530 SILVER MAPLE DR LA HABRA', 7, N'Pick', N'08:22', 30120)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (5, 20200925, N'811930', 124521, 31081979, N'925 W LAMBERT RD BREA', 8, N'Drop', N'08:39', 31170)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (6, 20200925, N'811930', 80064, 31082042, N'925 W LAMBERT RD BREA', 8, N'Drop', N'08:39', 31171)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (7, 20200925, N'811930', 107443, 31093387, N'925 W LAMBERT RD BREA', 8, N'Drop', N'08:40', 31200)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (8, 20200925, N'811930', 143114, 31082288, N'3100 E BIRCH ST BREA', 8, N'Drop', N'08:59', 32340)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (9, 20200925, N'811930', 133503, 31082092, N'1721 N GREENGROVE ST ORANGE', 8, N'Pick', N'09:32', 34320)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (10, 20200925, N'811930', 58495, 31093596, N'1126 N CENTER ST ORANGE', 8, N'Pick', N'09:45', 35100)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (11, 20200925, N'811930', 114035, 31094026, N'12792 ARLETTA CIR GARDEN GROVE', 8, N'Pick', N'10:01', 36090)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (12, 20200925, N'811930', 58495, 31093596, N'2043 N BROADWAY SANTA ANA', 10, N'Drop', N'10:15', 36900)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (13, 20200925, N'811930', 133503, 31082092, N'2043 N BROADWAY SANTA ANA', 10, N'Drop', N'10:15', 36901)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (14, 20200925, N'811930', 114035, 31094026, N'2043 N BROADWAY SANTA ANA', 10, N'Drop', N'10:15', 36902)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (15, 20200925, N'811930', 9956, 31100748, N'1002 SECRETARIAT CIR COSTA MESA', 10, N'Pick', N'11:08', 40080)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (16, 20200925, N'811930', 9956, 31100748, N'3650 S BRISTOL ST SANTA ANA', 11, N'Drop', N'11:15', 40500)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (17, 20200925, N'811930', 108459, 31101365, N'7802 BARTON DR HUNTINGTON BEACH', 11, N'Pick', N'12:29', 44970)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (18, 20200925, N'811930', 108459, 31101365, N'1201 W LA VETA AVE ORANGE', 13, N'Drop', N'13:10', 47400)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (19, 20200925, N'811930', 147908, 31098948, N'105 E KATELLA AV ORANGE', 13, N'Pick', N'13:33', 48780)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (20, 20200925, N'811930', 147908, 31098948, N'1621 PORTOLA AVE SANTA ANA', 14, N'Drop', N'14:00', 50400)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (21, 20200925, N'811930', 62654, 31082267, N'2043 N BROADWAY SANTA ANA', 13, N'Pick', N'14:22', 51750)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (22, 20200925, N'811930', 62654, 31082267, N'2305 N HARBOR BLVD FULLERTON', 15, N'Drop', N'15:07', 54420)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (23, 20200925, N'811930', 145859, 31100672, N'1901 CARNEGIE AVE SANTA ANA', 16, N'Pick', N'16:34', 59640)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (24, 20200925, N'811930', 145859, 31100672, N'1101 SYCAMORE AVE TUSTIN', 16, N'Drop', N'16:53', 60780)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (1, 20200925, N'812030', 133181, 31094716, N'1321 W 9TH ST SANTA ANA', 6, N'Pick', N'06:13', 22380)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (2, 20200925, N'812030', 133181, 31094716, N'1221 W COAST HWY NEWPORT BEACH', 6, N'Drop', N'06:44', 24240)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (3, 20200925, N'812030', 89000, 31082260, N'867 W 19TH ST COSTA MESA', 7, N'Pick', N'07:35', 27330)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (4, 20200925, N'812030', 15277, 31081890, N'3112 PIERCE AV COSTA MESA', 7, N'Pick', N'07:53', 28380)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (5, 20200925, N'812030', 95854, 31101704, N'2532 SANTA CATALINA DR COSTA MESA', 7, N'Pick', N'08:10', 29430)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (6, 20200925, N'812030', 54069, 31081983, N'2556 SANTA BARBARA LN COSTA MESA', 8, N'Pick', N'08:14', 29640)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (7, 20200925, N'812030', 66785, 31081413, N'2500 CHRISTOPHER LN COSTA MESA', 8, N'Pick', N'08:21', 30060)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (8, 20200925, N'812030', 141942, 31082053, N'2525 SANTA CATALINA DR COSTA MESA', 8, N'Pick', N'08:25', 30330)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (9, 20200925, N'812030', 141942, 31082053, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:40', 31200)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (10, 20200925, N'812030', 89000, 31082260, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:40', 31230)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (11, 20200925, N'812030', 54069, 31081983, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:40', 31231)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (12, 20200925, N'812030', 15277, 31081890, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:40', 31232)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (13, 20200925, N'812030', 66785, 31081413, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', N'08:40', 31233)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (14, 20200925, N'812030', 146070, 31100699, N'1919 E FRUIT ST SANTA ANA', 8, N'Pick', N'09:13', 33180)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (15, 20200925, N'812030', 95854, 31101704, N'1601 E ST ANDREW PL SANTA ANA', 9, N'Drop', N'09:30', 34200)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (16, 20200925, N'812030', 146070, 31100699, N'101 THE CITY DR S ORANGE', 10, N'Drop', N'10:03', 36210)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (17, 20200925, N'812030', 129374, 31098031, N'10932 PALMA VISTA AVE GARDEN GROVE', 10, N'Pick', N'11:13', 40381)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (18, 20200925, N'812030', 129374, 31098031, N'8887 WESTMINSTER AVE GARDEN GROVE', 11, N'Drop', N'11:13', 40382)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (19, 20200925, N'812030', 145806, 31082305, N'9856 WESTMINSTER BLVD GARDEN GROVE', 11, N'Pick', N'11:42', 42120)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (20, 20200925, N'812030', 145888, 31082303, N'9856 WESTMINSTER BLVD GARDEN GROVE', 11, N'Pick', N'11:42', 42121)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (21, 20200925, N'812030', 130126, 31082320, N'9856 WESTMINSTER BLVD GARDEN GROVE', 11, N'Pick', N'11:43', 42181)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (22, 20200925, N'812030', 133679, 31082324, N'9856 WESTMINSTER BLVD GARDEN GROVE', 11, N'Pick', N'11:43', 42182)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (23, 20200925, N'812030', 133449, 31093738, N'9856 WESTMINSTER BLVD GARDEN GROVE', 11, N'Pick', N'11:43', 42183)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (24, 20200925, N'812030', 145806, 31082305, N'9800 BOLSA AVE WESTMINSTER', 11, N'Drop', N'11:52', 42750)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (25, 20200925, N'812030', 133679, 31082324, N'110 N COOPER ST SANTA ANA', 12, N'Drop', N'12:00', 43200)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (26, 20200925, N'812030', 130126, 31082320, N'15591 HORTENSE DR WESTMINSTER', 12, N'Drop', N'12:08', 43710)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (27, 20200925, N'812030', 133449, 31093738, N'16418 MT NEWBERRY CIR FOUNTAIN VALLEY', 12, N'Drop', N'12:20', 44430)
GO
INSERT [dbo].[TABLETESTDATA] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec]) VALUES (28, 20200925, N'812030', 145888, 31082303, N'10649 EL TORO AVE FOUNTAIN VALLEY', 12, N'Drop', N'12:29', 44940)
GO

Below is the query I used to create the "GRP" column:

SELECT G.LDate,G.EvstrName,G.Address, G.BookingID, G.EstTime, G.EstTimeSec
,(G.RN - ROW_NUMBER() OVER (PARTITION BY LDate,EvStrName,[Event_Activity] ORDER BY LDate,EvStrName,RN)) GRP
FROM
(SELECT a.Trips,b.LDate,a.EvStrName,a.[Address],b.BookingID,B.ClientID,A.[PickDropHour],B.EstTime,B.EstTimeSec,B.[Event_Activity],b.rn
FROM
(SELECT COUNT(1) Trips,LDate, EvStrName, [Address],[PickDropHour],[Event_Activity]
FROM dbo.TABLETESTDATA 
GROUP BY LDate, EvStrName, [Address],[PickDropHour],[Event_Activity]
HAVING COUNT(1) >= 3
) A
LEFT JOIN
(SELECT LDate,EvStrName,[Address],BookingID,ClientID,[PickDropHour],EstTime,EstTimeSec,[Event_Activity],RN
FROM dbo.TABLETESTDATA 
) B ON A.LDate = B.LDate AND A.EvStrName = B.EvStrName AND A.[Address] = B.[Address] and A.[Event_Activity] = B.[Event_Activity] AND A.[PickDropHour] = B.[PickDropHour]) G
ORDER BY G.LDate, G.EvStrName, G.EstTimeSec

Ultimately, I need to determine these groups properly because I will need to get the Min(EstTimeSec) and Max(EstTimeSec) of each group so I can determine how many minutes it took between the first pick up to the last drop off, for each group, for each route.

I tried adding a column with a query that identifies if the BookingID of each pick event is in the drop event then 1, just to identify those that are part of the same group, but that doesn't quite get what I need to renumber the groups correctly.

Can anyone see what is needed here to properly consolidate these groups so they display how I need them?

Thanks so much!

April 9, 2022 at 9:52 am

This was removed by the editor as SPAM
April 26, 2022 at 9:13 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2072 More actions · Answer 1

One way to select intervals where the drop off grouping is hierarchically preferred to the pick up grouping could be to divide the data into two groups: Group1 = BookingID's dropped off in a group of 3 or more, Group2 = BookingID's not dropped off in a group of 3 or more. For Group1 the pick ups would all be assigned their grouping based on drop off. For Group2 groupings could be assigned based on 3+ picks ups at the same location. Why round 'PickDropHour' to the hour? Do you not have the 2 datetime columns: pick up and drop off?

This reply was modified 2 years, 1 month ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

tacy.highland Hall of Fame Points: 3319 More actions · Answer 2

How would your option allow me to calculate the full travel time of the entire group? Wouldn't it just give the travel time per each group instead of the whole? How would it work with Min/Max times to get the full travel time?

The PickDropHour is just for use in ordering. Picks have a pick time, drops have a drop time, so a single value to represent both will allow me to order them in hour time order. I can also do counts of picks and drops separately, grouped by this hour value.

Steve Collins SSCrazy Points: 2072 More actions · Answer 3

We're trying to identify groupings in events, no? You've yet to describe or list the exact output you're looking for from the query. Route 810030 has Grp 10, 16, and 26. What should the values be? Regarding your follow on questions. The table provided has no uniqueness constraints whatsoever. To answer questions would require running a bunch of confirmatory queries first. What's the SQL question tho?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

tacy.highland Hall of Fame Points: 3319 More actions · Answer 4

Yes, I'm looking for a group number for each group. As long as they're some type of number/value that designates the grouping of trips together, I don't care what the actual number is. Once I can get that group number or value, I can then pull in and calculate the min(time) and max(time) to calculate the full travel time for each of those groups, (because I'll be able to group by the new group number). Moving even further down the road, once I have that time value for each group, I can then move on to determining averages by group size (number of trips).

So that's all I'm looking for, is this group number/value that designates each "grouping" on each route. If you can determine overall groups with the "inner" groups as they are showing up now, I'd be really interested to see how you might do that.

On the uniqueness, the ldate, route, and rn (row number for each event on the route in order) would be unique identifiers, and then of course the unique BookingIDs for each trip. Does that help?

Thanks!

drew.allen SSC Guru Points: 76945 More actions · Answer 5

You haven't given expected results, but this at least groups the records you wanted together. The approach I took was to find the minimum booking id for each event (pick/drop) and then find that minimum for each client. It's possible that you might need a couple of iterations to achieve a final result.

WITH MinEventBooking AS -- Find the minimum booking for each event
(
    SELECT d.RN
         , d.LDate
         , d.EvstrName
         , d.ClientID
         , d.BookingID
         , d.Address
         , d.PickDropHour
         , d.Event_Activity
         , d.EstTime
         , d.EstTimeSec
         , MIN(d.BookingID) OVER(PARTITION BY d.LDate, d.EvstrName, d.Event_Activity, d.EstTime) AS MinEventBooking
    FROM #TableTestData AS d
)
SELECT e.RN
        , e.LDate
        , e.EvstrName
        , e.ClientID
        , e.BookingID
        , e.Address
        , e.PickDropHour
        , e.Event_Activity
        , e.EstTime
        , e.EstTimeSec
        , MIN(e.MinEventBooking) OVER(PARTITION BY e.BookingID) AS GroupBookingID
FROM MinEventBooking AS e
ORDER BY e.EvstrName, e.LDate, e.EstTime

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

tacy.highland Hall of Fame Points: 3319 More actions · Answer 6

Thanks Drew! I would say the format of the results you provided would work fine since it's assigning a value to all the trips which belong together in the grouping, but your query didn't quite pick up all the expected groupings. It's very close though, most of the groupings are perfectly identified. To be clear, the BookingIDs aren't designating any order, they're just a unique trip ID so I'm not sure how the Min/Max works with that but maybe I'm just not seeing something.

Your query picked up the groupings for Evstrname 810030, but it completely missed a grouping on 811930 and did some weird things with the last grouping on 812030.

For example, for Evstrname 811930, the first 8 records are one grouping since the first person picked up at 7:15 is dropping at 925 W Lambert with 2 other people, satisfying the minimum 3 at an address. So the records RN 1 through RN7 should be designated as a grouping, but they're not. The last group on 812030 starts with a pick up at RN20 at 9856 Westminster and everyone in that grouping get dropped at RN28 (the last trip), but it seems to be divided into two groupings for some reason.

Some more explanation on expected results: If person 1 is picked up going to group address 1 and then others are picked up along the way, (some also going to group address 1 but some not), then the grouping should start with person 1 trip and also be for each of the picks going to the address and then the drops at the group address 1, like is the case with how your query pulled up groupings for EvStrName 812030 between RN3 to RN10. This is great. So I know exactly how many trips are in the group, even if other people are picked up along the way that are not part of the group. But the second grouping on that EvStrName doesn't get picked up the same way. The first person in the group (RN 20) is picked up but not listed as part of the grouping, (and neither is the second person).

Does that clarify at all? The result set layout looks perfect, it's just that it's not quite picking out the groups correctly.

Thank you so much for your eyes on this.

drew.allen SSC Guru Points: 76945 More actions · Answer 7

The purpose of the MIN/MAX is to assign a unique ID to each group. It doesn't matter if the IDs are ordered or not, it's just a way of picking a unique ID.

You know the saying a picture is worth a thousand words? Well, in SQL, code is worth a million words. Instead of using English to describe your expected results write a Table Value Constructor that will produce the expected results.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

tacy.highland Hall of Fame Points: 3319 More actions · Answer 8

Here's the expected results, based on the query that you provided above:

CREATE TABLE [dbo].[GroupingExpectedResults](
[RN] [float] NULL,
[LDate] [float] NULL,
[EvstrName] [float] NULL,
[ClientID] [float] NULL,
[BookingID] [float] NULL,
[Address] [nvarchar](255) NULL,
[PickDropHour] [float] NULL,
[Event_Activity] [nvarchar](255) NULL,
[EstTime] [datetime] NULL,
[EstTimeSec] [float] NULL,
[GroupBookingID] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (1, 20220401, 810030, 109282, 31081478, N'470 N WALNUT ST LA HABRA', 4, N'Pick', CAST(N'1899-12-30T04:09:00.000' AS DateTime), 14970, 31081478)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (2, 20220401, 810030, 109282, 31081478, N'595 TAMARACK AVE BREA', 4, N'Drop', CAST(N'1899-12-30T04:20:00.000' AS DateTime), 15630, 31081478)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (3, 20220401, 810030, 54570, 31102067, N'315 W LINCOLN AVE ORANGE', 4, N'Pick', CAST(N'1899-12-30T04:55:00.000' AS DateTime), 17700, 31102067)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (4, 20220401, 810030, 54570, 31102067, N'2684 N TUSTIN ST ORANGE', 4, N'Drop', CAST(N'1899-12-30T04:59:00.000' AS DateTime), 17970, 31102067)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (5, 20220401, 810030, 63480, 31102123, N'520 N BREA BLVD BREA', 5, N'Pick', CAST(N'1899-12-30T05:34:00.000' AS DateTime), 20040, 31102123)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (6, 20220401, 810030, 63480, 31102123, N'410 N FAIRVIEW ST SANTA ANA', 5, N'Drop', CAST(N'1899-12-30T05:54:00.000' AS DateTime), 21270, 31102123)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (7, 20220401, 810030, 140108, 31082123, N'1140 W CUBBON ST SANTA ANA', 6, N'Pick', CAST(N'1899-12-30T06:32:00.000' AS DateTime), 23550, 31082123)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (8, 20220401, 810030, 140108, 31082123, N'17197 NEWHOPE ST FOUNTAIN VALLEY', 6, N'Drop', CAST(N'1899-12-30T06:47:00.000' AS DateTime), 24450, 31082123)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (9, 20220401, 810030, 100593, 31081767, N'15411 VERMONT ST WESTMINSTER', 7, N'Pick', CAST(N'1899-12-30T07:23:00.000' AS DateTime), 26580, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (10, 20220401, 810030, 114497, 31081576, N'8791 MONTEREY CIR WESTMINSTER', 7, N'Pick', CAST(N'1899-12-30T07:32:00.000' AS DateTime), 27150, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (12, 20220401, 810030, 139972, 31081601, N'9791 CORNWALL AVE WESTMINSTER', 7, N'Pick', CAST(N'1899-12-30T07:45:00.000' AS DateTime), 27901, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (11, 20220401, 810030, 137816, 31081566, N'9791 CORNWALL AVE WESTMINSTER', 7, N'Pick', CAST(N'1899-12-30T07:45:00.000' AS DateTime), 27900, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (13, 20220401, 810030, 67676, 31081414, N'9791 CORNWALL AVE WESTMINSTER', 7, N'Pick', CAST(N'1899-12-30T07:45:00.000' AS DateTime), 27930, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (14, 20220401, 810030, 129805, 31081921, N'9791 CORNWALL AVE WESTMINSTER', 7, N'Pick', CAST(N'1899-12-30T07:45:00.000' AS DateTime), 27931, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (15, 20220401, 810030, 112188, 31081855, N'16098 BANANA ST FOUNTAIN VALLEY', 7, N'Pick', CAST(N'1899-12-30T07:55:00.000' AS DateTime), 28500, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (16, 20220401, 810030, 143945, 31081662, N'15111 BUSHARD ST WESTMINSTER', 8, N'Pick', CAST(N'1899-12-30T08:01:00.000' AS DateTime), 28890, 31081662)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (21, 20220401, 810030, 114497, 31081576, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:19:00.000' AS DateTime), 29944, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (22, 20220401, 810030, 139972, 31081601, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:19:00.000' AS DateTime), 29945, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (20, 20220401, 810030, 100593, 31081767, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:19:00.000' AS DateTime), 29943, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (19, 20220401, 810030, 67676, 31081414, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:19:00.000' AS DateTime), 29942, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (23, 20220401, 810030, 137816, 31081566, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:19:00.000' AS DateTime), 29946, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (17, 20220401, 810030, 112188, 31081855, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:19:00.000' AS DateTime), 29940, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (18, 20220401, 810030, 129805, 31081921, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:19:00.000' AS DateTime), 29941, 31081414)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (24, 20220401, 810030, 143945, 31081662, N'17150 EUCLID ST FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:26:00.000' AS DateTime), 30360, 31081662)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (25, 20220401, 810030, 11731, 31100557, N'2553 ANACAPA DR COSTA MESA', 10, N'Pick', CAST(N'1899-12-30T10:17:00.000' AS DateTime), 37050, 31100557)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (26, 20220401, 810030, 93149, 31101174, N'11180 WARNER AVE FOUNTAIN VALLEY', 10, N'Pick', CAST(N'1899-12-30T10:32:00.000' AS DateTime), 37950, 31101174)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (27, 20220401, 810030, 93149, 31101174, N'2117 W MYRTLE ST SANTA ANA', 10, N'Drop', CAST(N'1899-12-30T10:48:00.000' AS DateTime), 38880, 31101174)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (28, 20220401, 810030, 11731, 31100557, N'101 THE CITY DR ORANGE', 11, N'Drop', CAST(N'1899-12-30T11:07:00.000' AS DateTime), 40050, 31100557)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (29, 20220401, 810030, 118744, 31100729, N'790 THE CITY DR S ORANGE', 11, N'Pick', CAST(N'1899-12-30T11:23:00.000' AS DateTime), 41010, 31100729)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (30, 20220401, 810030, 118744, 31100729, N'224 E STANFORD ST SANTA ANA', 11, N'Drop', CAST(N'1899-12-30T11:42:00.000' AS DateTime), 42120, 31100729)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (31, 20220401, 810030, 91990, 31082233, N'295 E YALE LOOP IRVINE', 13, N'Pick', CAST(N'1899-12-30T13:39:00.000' AS DateTime), 49140, 31081555)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (34, 20220401, 810030, 72627, 31082475, N'295 E YALE LOOP IRVINE', 13, N'Pick', CAST(N'1899-12-30T13:39:00.000' AS DateTime), 49143, 31081555)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (32, 20220401, 810030, 145875, 31081777, N'295 E YALE LOOP IRVINE', 13, N'Pick', CAST(N'1899-12-30T13:39:00.000' AS DateTime), 49141, 31081555)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (33, 20220401, 810030, 43621, 31081555, N'295 E YALE LOOP IRVINE', 13, N'Pick', CAST(N'1899-12-30T13:39:00.000' AS DateTime), 49142, 31081555)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (35, 20220401, 810030, 145875, 31081777, N'14741 HOLT AVE TUSTIN', 14, N'Drop', CAST(N'1899-12-30T14:03:00.000' AS DateTime), 50580, 31081555)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (36, 20220401, 810030, 91990, 31082233, N'400 S SUNKIST ST ANAHEIM', 14, N'Drop', CAST(N'1899-12-30T14:27:00.000' AS DateTime), 52050, 31081555)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (37, 20220401, 810030, 43621, 31081555, N'400 S SUNKIST ST ANAHEIM', 14, N'Drop', CAST(N'1899-12-30T14:28:00.000' AS DateTime), 52110, 31081555)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (38, 20220401, 810030, 72627, 31082475, N'1166 N CITRON ST ANAHEIM', 14, N'Drop', CAST(N'1899-12-30T14:43:00.000' AS DateTime), 53010, 31081555)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (1, 20220401, 811930, 107443, 31093387, N'1342 W ELM AVE FULLERTON', 7, N'Pick', CAST(N'1899-12-30T07:15:00.000' AS DateTime), 26130, 31081979)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (2, 20220401, 811930, 80064, 31082042, N'117 EDWARD AVE FULLERTON', 7, N'Pick', CAST(N'1899-12-30T07:32:00.000' AS DateTime), 27150, 31081979)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (3, 20220401, 811930, 143114, 31082288, N'400 W ORANGETHORPE AVE FULLERTON', 7, N'Pick', CAST(N'1899-12-30T07:49:00.000' AS DateTime), 28170, 31082288)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (4, 20220401, 811930, 124521, 31081979, N'1530 SILVER MAPLE DR LA HABRA', 7, N'Pick', CAST(N'1899-12-30T08:22:00.000' AS DateTime), 30120, 31081979)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (5, 20220401, 811930, 124521, 31081979, N'925 W LAMBERT RD BREA', 8, N'Drop', CAST(N'1899-12-30T08:39:00.000' AS DateTime), 31170, 31081979)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (6, 20220401, 811930, 80064, 31082042, N'925 W LAMBERT RD BREA', 8, N'Drop', CAST(N'1899-12-30T08:39:00.000' AS DateTime), 31171, 31081979)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (7, 20220401, 811930, 107443, 31093387, N'925 W LAMBERT RD BREA', 8, N'Drop', CAST(N'1899-12-30T08:40:00.000' AS DateTime), 31200, 31081979)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (8, 20220401, 811930, 143114, 31082288, N'3100 E BIRCH ST BREA', 8, N'Drop', CAST(N'1899-12-30T08:59:00.000' AS DateTime), 32340, 31082288)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (9, 20220401, 811930, 133503, 31082092, N'1721 N GREENGROVE ST ORANGE', 8, N'Pick', CAST(N'1899-12-30T09:32:00.000' AS DateTime), 34320, 31082092)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (10, 20220401, 811930, 58495, 31093596, N'1126 N CENTER ST ORANGE', 8, N'Pick', CAST(N'1899-12-30T09:45:00.000' AS DateTime), 35100, 31082092)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (11, 20220401, 811930, 114035, 31094026, N'12792 ARLETTA CIR GARDEN GROVE', 8, N'Pick', CAST(N'1899-12-30T10:01:00.000' AS DateTime), 36090, 31082092)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (14, 20220401, 811930, 114035, 31094026, N'2043 N BROADWAY SANTA ANA', 10, N'Drop', CAST(N'1899-12-30T10:15:00.000' AS DateTime), 36902, 31082092)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (12, 20220401, 811930, 58495, 31093596, N'2043 N BROADWAY SANTA ANA', 10, N'Drop', CAST(N'1899-12-30T10:15:00.000' AS DateTime), 36900, 31082092)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (13, 20220401, 811930, 133503, 31082092, N'2043 N BROADWAY SANTA ANA', 10, N'Drop', CAST(N'1899-12-30T10:15:00.000' AS DateTime), 36901, 31082092)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (15, 20220401, 811930, 9956, 31100748, N'1002 SECRETARIAT CIR COSTA MESA', 10, N'Pick', CAST(N'1899-12-30T11:08:00.000' AS DateTime), 40080, 31100748)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (16, 20220401, 811930, 9956, 31100748, N'3650 S BRISTOL ST SANTA ANA', 11, N'Drop', CAST(N'1899-12-30T11:15:00.000' AS DateTime), 40500, 31100748)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (17, 20220401, 811930, 108459, 31101365, N'7802 BARTON DR HUNTINGTON BEACH', 11, N'Pick', CAST(N'1899-12-30T12:29:00.000' AS DateTime), 44970, 31101365)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (18, 20220401, 811930, 108459, 31101365, N'1201 W LA VETA AVE ORANGE', 13, N'Drop', CAST(N'1899-12-30T13:10:00.000' AS DateTime), 47400, 31101365)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (19, 20220401, 811930, 147908, 31098948, N'105 E KATELLA AV ORANGE', 13, N'Pick', CAST(N'1899-12-30T13:33:00.000' AS DateTime), 48780, 31098948)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (20, 20220401, 811930, 147908, 31098948, N'1621 PORTOLA AVE SANTA ANA', 14, N'Drop', CAST(N'1899-12-30T14:00:00.000' AS DateTime), 50400, 31098948)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (21, 20220401, 811930, 62654, 31082267, N'2043 N BROADWAY SANTA ANA', 13, N'Pick', CAST(N'1899-12-30T14:22:00.000' AS DateTime), 51750, 31082267)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (22, 20220401, 811930, 62654, 31082267, N'2305 N HARBOR BLVD FULLERTON', 15, N'Drop', CAST(N'1899-12-30T15:07:00.000' AS DateTime), 54420, 31082267)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (23, 20220401, 811930, 145859, 31100672, N'1901 CARNEGIE AVE SANTA ANA', 16, N'Pick', CAST(N'1899-12-30T16:34:00.000' AS DateTime), 59640, 31100672)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (24, 20220401, 811930, 145859, 31100672, N'1101 SYCAMORE AVE TUSTIN', 16, N'Drop', CAST(N'1899-12-30T16:53:00.000' AS DateTime), 60780, 31100672)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (1, 20220401, 812030, 133181, 31094716, N'1321 W 9TH ST SANTA ANA', 6, N'Pick', CAST(N'1899-12-30T06:13:00.000' AS DateTime), 22380, 31094716)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (2, 20220401, 812030, 133181, 31094716, N'1221 W COAST HWY NEWPORT BEACH', 6, N'Drop', CAST(N'1899-12-30T06:44:00.000' AS DateTime), 24240, 31094716)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (3, 20220401, 812030, 89000, 31082260, N'867 W 19TH ST COSTA MESA', 7, N'Pick', CAST(N'1899-12-30T07:35:00.000' AS DateTime), 27330, 31081413)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (4, 20220401, 812030, 15277, 31081890, N'3112 PIERCE AV COSTA MESA', 7, N'Pick', CAST(N'1899-12-30T07:53:00.000' AS DateTime), 28380, 31081413)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (5, 20220401, 812030, 95854, 31101704, N'2532 SANTA CATALINA DR COSTA MESA', 7, N'Pick', CAST(N'1899-12-30T08:10:00.000' AS DateTime), 29430, 31101704)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (6, 20220401, 812030, 54069, 31081983, N'2556 SANTA BARBARA LN COSTA MESA', 8, N'Pick', CAST(N'1899-12-30T08:14:00.000' AS DateTime), 29640, 31081413)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (7, 20220401, 812030, 66785, 31081413, N'2500 CHRISTOPHER LN COSTA MESA', 8, N'Pick', CAST(N'1899-12-30T08:21:00.000' AS DateTime), 30060, 31081413)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (8, 20220401, 812030, 141942, 31082053, N'2525 SANTA CATALINA DR COSTA MESA', 8, N'Pick', CAST(N'1899-12-30T08:25:00.000' AS DateTime), 30330, 31081413)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (9, 20220401, 812030, 141942, 31082053, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:40:00.000' AS DateTime), 31200, 31081413)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (12, 20220401, 812030, 15277, 31081890, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:40:00.000' AS DateTime), 31232, 31081413)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (11, 20220401, 812030, 54069, 31081983, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:40:00.000' AS DateTime), 31231, 31081413)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (13, 20220401, 812030, 66785, 31081413, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:40:00.000' AS DateTime), 31233, 31081413)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (10, 20220401, 812030, 89000, 31082260, N'18325 MOUNT BALDY CIR FOUNTAIN VALLEY', 8, N'Drop', CAST(N'1899-12-30T08:40:00.000' AS DateTime), 31230, 31081413)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (14, 20220401, 812030, 146070, 31100699, N'1919 E FRUIT ST SANTA ANA', 8, N'Pick', CAST(N'1899-12-30T09:13:00.000' AS DateTime), 33180, 31100699)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (15, 20220401, 812030, 95854, 31101704, N'1601 E ST ANDREW PL SANTA ANA', 9, N'Drop', CAST(N'1899-12-30T09:30:00.000' AS DateTime), 34200, 31101704)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (16, 20220401, 812030, 146070, 31100699, N'101 THE CITY DR S ORANGE', 10, N'Drop', CAST(N'1899-12-30T10:03:00.000' AS DateTime), 36210, 31100699)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (17, 20220401, 812030, 129374, 31098031, N'10932 PALMA VISTA AVE GARDEN GROVE', 10, N'Pick', CAST(N'1899-12-30T11:13:00.000' AS DateTime), 40381, 31098031)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (18, 20220401, 812030, 129374, 31098031, N'8887 WESTMINSTER AVE GARDEN GROVE', 11, N'Drop', CAST(N'1899-12-30T11:13:00.000' AS DateTime), 40382, 31098031)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (20, 20220401, 812030, 145888, 31082303, N'9856 WESTMINSTER BLVD GARDEN GROVE', 11, N'Pick', CAST(N'1899-12-30T11:42:00.000' AS DateTime), 42121, 31082320)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (19, 20220401, 812030, 145806, 31082305, N'9856 WESTMINSTER BLVD GARDEN GROVE', 11, N'Pick', CAST(N'1899-12-30T11:42:00.000' AS DateTime), 42120, 31082320)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (21, 20220401, 812030, 130126, 31082320, N'9856 WESTMINSTER BLVD GARDEN GROVE', 11, N'Pick', CAST(N'1899-12-30T11:43:00.000' AS DateTime), 42181, 31082320)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (22, 20220401, 812030, 133679, 31082324, N'9856 WESTMINSTER BLVD GARDEN GROVE', 11, N'Pick', CAST(N'1899-12-30T11:43:00.000' AS DateTime), 42182, 31082320)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (23, 20220401, 812030, 133449, 31093738, N'9856 WESTMINSTER BLVD GARDEN GROVE', 11, N'Pick', CAST(N'1899-12-30T11:43:00.000' AS DateTime), 42183, 31082320)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (24, 20220401, 812030, 145806, 31082305, N'9800 BOLSA AVE WESTMINSTER', 11, N'Drop', CAST(N'1899-12-30T11:52:00.000' AS DateTime), 42750, 31082320)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (25, 20220401, 812030, 133679, 31082324, N'110 N COOPER ST SANTA ANA', 12, N'Drop', CAST(N'1899-12-30T12:00:00.000' AS DateTime), 43200, 31082320)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (26, 20220401, 812030, 130126, 31082320, N'15591 HORTENSE DR WESTMINSTER', 12, N'Drop', CAST(N'1899-12-30T12:08:00.000' AS DateTime), 43710, 31082320)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (27, 20220401, 812030, 133449, 31093738, N'16418 MT NEWBERRY CIR FOUNTAIN VALLEY', 12, N'Drop', CAST(N'1899-12-30T12:20:00.000' AS DateTime), 44430, 31082320)
GO
INSERT [dbo].[GroupingExpectedResults] ([RN], [LDate], [EvstrName], [ClientID], [BookingID], [Address], [PickDropHour], [Event_Activity], [EstTime], [EstTimeSec], [GroupBookingID]) VALUES (28, 20220401, 812030, 145888, 31082303, N'10649 EL TORO AVE FOUNTAIN VALLEY', 12, N'Drop', CAST(N'1899-12-30T12:29:00.000' AS DateTime), 44940, 31082320)
GO

Thanks

tacy.highland Hall of Fame Points: 3319 More actions · Answer 9

Any other ideas out there to get the expected results table above? The query Drew provided was so close....