Count of Distinct Clients in a list, in groups

Question

Post reply

Count of Distinct Clients in a list, in groups

tacy.highland

Hall of Fame

Points: 3319

January 22, 2020 at 6:40 pm

#3717522

Hello all,

I have a dataset of trip information (the majority of which I got from another awesome forum member not long ago) where I need to be able to get a distinct count of passengers in the groups when someone is onboard the vehicle. This trip information shows when a vehicle is picking up and dropping off passengers so there are some times when a rider may be sharing the ride with other passengers, in which case, there are multiple people onboard at one time. One of the columns included does show when there are passengers onboard (PassOB) and there is a clientID column which shows the actual passengers. What I need to do is be able to do a distinct count of passengers in each group where someone is onboard.

On the surface this seems like it would be a relatively simple process (and maybe it is??) but I just can't figure it out. Here is the test data as-is:

CREATE TABLE [dbo].[__tripinfo](
[LDate] [int] NULL,
[TripDate] [char](10) NULL,
[RN] [bigint] NULL,
[BookingID] [int] NULL,
[Route] [varchar](20) NULL,
[Event_Activity] [varchar](4) NULL,
[EvOrder] [int] NULL,
[ClientID] [int] NULL,
[PassOB] [varchar](50) NULL,
[SpaceOB] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 1, 9977026, N'100', N'Pick', 16666698, 65250, N'CLI1', N'AM1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 2, 9977447, N'100', N'Pick', 30952407, 75030, N'CLI2', N'AM2')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 3, 9977827, N'100', N'Pick', 33809569, 55275, N'CLI3', N'AM3')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 4, 9976922, N'100', N'Pick', 43452396, 22729, N'CLI4', N'AM4')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 5, 9976996, N'100', N'Pick', 54252207, 26192, N'CLI5', N'AM5')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 6, 9976998, N'100', N'Pick', 62352047, 50205, N'CLI6', N'AM6')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 7, 9977104, N'100', N'Pick', 72983124, 71409, N'CLI7', N'AM7')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 8, 9977108, N'100', N'Pick', 76400231, 52806, N'CLI8', N'AM8')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 9, 9977755, N'100', N'Pick', 78963106, 39327, N'CLI9', N'AM9')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 10, 9977026, N'100', N'Drop', 80885231, 65250, N'CLI8', N'AM8')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 11, 9976922, N'100', N'Drop', 82326807, 22729, N'CLI7', N'AM7')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 12, 9977827, N'100', N'Drop', 83408020, 55275, N'CLI6', N'AM6')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 13, 9976996, N'100', N'Drop', 84218893, 26192, N'CLI5', N'AM5')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 14, 9977104, N'100', N'Drop', 84827057, 71409, N'CLI4', N'AM4')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 15, 9977108, N'100', N'Drop', 85283198, 52806, N'CLI3', N'AM3')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 16, 9977755, N'100', N'Drop', 85625317, 39327, N'CLI2', N'AM2')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 17, 9976998, N'100', N'Drop', 85881871, 50205, N'CLI1', N'AM1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 18, 9977447, N'100', N'Drop', 86074327, 75030, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 19, 9977888, N'100', N'Pick', 96810358, 72467, N'CLI1', N'XW1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 20, 9977888, N'100', N'Drop', 97013805, 72467, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 21, 9986778, N'100', N'Pick', 97069755, 46029, N'CLI1', N'AM1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 22, 9986778, N'100', N'Drop', 97100574, 46029, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 23, 9987355, N'100', N'Pick', 97445354, 71459, N'CLI1', N'LR1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 24, 9987355, N'100', N'Drop', 97638581, 71459, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 1, 9977487, N'101A', N'Pick', 39062527, 49445, N'CLI1', N'WH1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 2, 9977487, N'101A', N'Drop', 40234399, 49445, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 3, 9977202, N'101A', N'Pick', 41113303, 60716, N'CLI1', N'WH1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 4, 9977087, N'101A', N'Pick', 41772496, 8199, N'CLI2', N'LR1,WH1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 5, 9977202, N'101A', N'Drop', 42266870, 60716, N'CLI1', N'LR1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 6, 9977087, N'101A', N'Drop', 42637652, 8199, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 7, 9986963, N'101A', N'Pick', 43471931, 78437, N'CLI1', N'LR1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 8, 9986963, N'101A', N'Drop', 43680478, 78437, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 1, 9977615, N'102', N'Pick', 25000028, 5152, N'CLI1,PCA1', N'AM1,WH1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 2, 9977480, N'102', N'Pick', 43750026, 61043, N'CLI2,PCA1', N'AM2,WH1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 3, 9977480, N'102', N'Drop', 57812527, 61043, N'CLI1,PCA1', N'AM1,WH1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 4, 9977615, N'102', N'Drop', 68359403, 5152, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 5, 9984724, N'102', N'Pick', 68979778, 83955, N'CLI1', N'XW1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 6, 9984724, N'102', N'Drop', 69425423, 83955, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 7, 9984906, N'102', N'Pick', 82549513, 58263, N'CLI1', N'AM1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 8, 9987359, N'102', N'Pick', 83575045, 74845, N'CLI2', N'AM2')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 9, 9977254, N'102', N'Pick', 86651643, 68974, N'CLI3', N'AM3')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 10, 9987359, N'102', N'Drop', 87485886, 74845, N'CLI2', N'AM2')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 11, 9977254, N'102', N'Drop', 89988737, 68974, N'CLI1', N'AM1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 12, 9984906, N'102', N'Drop', 93312775, 58263, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 13, 9977770, N'102', N'Pick', 93928726, 82886, N'CLI1', N'WH1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 14, 9984747, N'102', N'Pick', 97178839, 46670, N'CLI2', N'WH2')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 15, 9977770, N'102', N'Drop', 98218219, 82886, N'CLI1', N'WH1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 16, 9984747, N'102', N'Drop', 98475756, 46670, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 1, 9987615, N'103A', N'Pick', 22309558, 621, N'CLI1', N'LR1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 2, 9987615, N'103A', N'Drop', 26500032, 621, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 3, 9984941, N'103A', N'Pick', 39071390, 83926, N'CLI1', N'LR1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 4, 9984941, N'103A', N'Drop', 43551572, 83926, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 5, 9987385, N'103A', N'Pick', 56992092, 62003, N'CLI1', N'LR1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 6, 9987385, N'103A', N'Drop', 58869436, 62003, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 7, 9987141, N'103A', N'Pick', 64501469, 65507, N'CLI1', N'WH1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 8, 9987141, N'103A', N'Drop', 72648606, 65507, N'', N'')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 9, 9988781, N'103A', N'Pick', 75364314, 79977, N'CLI1,PCA1', N'AM1,WH1')
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 10, 9988781, N'103A', N'Drop', 76043233, 79977, N'', N'')
GO

This is what I would like to see/get, with the ClientCount column added at the end:

CREATE TABLE [dbo].[__tripinfo](
[LDate] [int] NULL,
[TripDate] [char](10) NULL,
[RN] [bigint] NULL,
[BookingID] [int] NULL,
[Route] [varchar](20) NULL,
[Event_Activity] [varchar](4) NULL,
[EvOrder] [int] NULL,
[ClientID] [int] NULL,
[PassOB] [varchar](50) NULL,
[SpaceOB] [varchar](50) NULL,
[ClientCount] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 1, 9977026, N'100', N'Pick', 16666698, 65250, N'CLI1', N'AM1', 9)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 2, 9977447, N'100', N'Pick', 30952407, 75030, N'CLI2', N'AM2', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 3, 9977827, N'100', N'Pick', 33809569, 55275, N'CLI3', N'AM3', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 4, 9976922, N'100', N'Pick', 43452396, 22729, N'CLI4', N'AM4', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 5, 9976996, N'100', N'Pick', 54252207, 26192, N'CLI5', N'AM5', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 6, 9976998, N'100', N'Pick', 62352047, 50205, N'CLI6', N'AM6', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 7, 9977104, N'100', N'Pick', 72983124, 71409, N'CLI7', N'AM7', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 8, 9977108, N'100', N'Pick', 76400231, 52806, N'CLI8', N'AM8', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 9, 9977755, N'100', N'Pick', 78963106, 39327, N'CLI9', N'AM9', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 10, 9977026, N'100', N'Drop', 80885231, 65250, N'CLI8', N'AM8', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 11, 9976922, N'100', N'Drop', 82326807, 22729, N'CLI7', N'AM7', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 12, 9977827, N'100', N'Drop', 83408020, 55275, N'CLI6', N'AM6', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 13, 9976996, N'100', N'Drop', 84218893, 26192, N'CLI5', N'AM5', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 14, 9977104, N'100', N'Drop', 84827057, 71409, N'CLI4', N'AM4', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 15, 9977108, N'100', N'Drop', 85283198, 52806, N'CLI3', N'AM3', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 16, 9977755, N'100', N'Drop', 85625317, 39327, N'CLI2', N'AM2', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 17, 9976998, N'100', N'Drop', 85881871, 50205, N'CLI1', N'AM1', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 18, 9977447, N'100', N'Drop', 86074327, 75030, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 19, 9977888, N'100', N'Pick', 96810358, 72467, N'CLI1', N'XW1', 1)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 20, 9977888, N'100', N'Drop', 97013805, 72467, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 21, 9986778, N'100', N'Pick', 97069755, 46029, N'CLI1', N'AM1', 1)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 22, 9986778, N'100', N'Drop', 97100574, 46029, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 23, 9987355, N'100', N'Pick', 97445354, 71459, N'CLI1', N'LR1', 1)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 24, 9987355, N'100', N'Drop', 97638581, 71459, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 1, 9977487, N'101A', N'Pick', 39062527, 49445, N'CLI1', N'WH1', 1)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 2, 9977487, N'101A', N'Drop', 40234399, 49445, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 3, 9977202, N'101A', N'Pick', 41113303, 60716, N'CLI1', N'WH1', 2)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 4, 9977087, N'101A', N'Pick', 41772496, 8199, N'CLI2', N'LR1,WH1', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 5, 9977202, N'101A', N'Drop', 42266870, 60716, N'CLI1', N'LR1', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 6, 9977087, N'101A', N'Drop', 42637652, 8199, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 7, 9986963, N'101A', N'Pick', 43471931, 78437, N'CLI1', N'LR1', 1)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 8, 9986963, N'101A', N'Drop', 43680478, 78437, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 1, 9977615, N'102', N'Pick', 25000028, 5152, N'CLI1,PCA1', N'AM1,WH1', 2)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 2, 9977480, N'102', N'Pick', 43750026, 61043, N'CLI2,PCA1', N'AM2,WH1', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 3, 9977480, N'102', N'Drop', 57812527, 61043, N'CLI1,PCA1', N'AM1,WH1', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 4, 9977615, N'102', N'Drop', 68359403, 5152, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 5, 9984724, N'102', N'Pick', 68979778, 83955, N'CLI1', N'XW1', 1)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 6, 9984724, N'102', N'Drop', 69425423, 83955, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 7, 9984906, N'102', N'Pick', 82549513, 58263, N'CLI1', N'AM1', 3)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 8, 9987359, N'102', N'Pick', 83575045, 74845, N'CLI2', N'AM2', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 9, 9977254, N'102', N'Pick', 86651643, 68974, N'CLI3', N'AM3', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 10, 9987359, N'102', N'Drop', 87485886, 74845, N'CLI2', N'AM2', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 11, 9977254, N'102', N'Drop', 89988737, 68974, N'CLI1', N'AM1', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 12, 9984906, N'102', N'Drop', 93312775, 58263, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 13, 9977770, N'102', N'Pick', 93928726, 82886, N'CLI1', N'WH1', 2)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 14, 9984747, N'102', N'Pick', 97178839, 46670, N'CLI2', N'WH2', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 15, 9977770, N'102', N'Drop', 98218219, 82886, N'CLI1', N'WH1', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 16, 9984747, N'102', N'Drop', 98475756, 46670, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 1, 9987615, N'103A', N'Pick', 22309558, 621, N'CLI1', N'LR1', 1)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 2, 9987615, N'103A', N'Drop', 26500032, 621, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 3, 9984941, N'103A', N'Pick', 39071390, 83926, N'CLI1', N'LR1', 1)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 4, 9984941, N'103A', N'Drop', 43551572, 83926, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 5, 9987385, N'103A', N'Pick', 56992092, 62003, N'CLI1', N'LR1', 1)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 6, 9987385, N'103A', N'Drop', 58869436, 62003, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 7, 9987141, N'103A', N'Pick', 64501469, 65507, N'CLI1', N'WH1', 1)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 8, 9987141, N'103A', N'Drop', 72648606, 65507, N'', N'', NULL)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 9, 9988781, N'103A', N'Pick', 75364314, 79977, N'CLI1,PCA1', N'AM1,WH1', 1)
GO
INSERT [dbo].[__tripinfo] ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB], [ClientCount]) VALUES (20200114, N'01/14/2020', 10, 9988781, N'103A', N'Drop', 76043233, 79977, N'', N'', NULL)
GO

This column, populated this way, will align with another column in the data (not shown) so having the client count like this will allow me to do additional analysis on the dataset. I'm guessing this can be done somehow using OVER/PARTITION BY?

Any insight would be very much appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

You were already given an answer to this in your previous thread.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

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

Drew, not quite. That previous thread provided a count of the number of events where a passenger is onboard with another passenger. So for example (this is actually in the sample data I provided on the first route), you pick up 9 different people and then drop each one off, it lists the number 17 in a separate column to reflect the number of events where at least 2 passengers are onboard together at the same time. What this is about is to get the actual number of people who were in that/each group, which in the above example would be 9. So it's close but not quite the same.

If the previous thread code was adjusted to do this count, that'd be great, but if not possible, it's not required. I looked at that code and tried to see how I could make changes to do this client count but couldn't figure it out. The previous code does provide miles and minutes for when riders are onboard (which is exactly what I needed), but that's just subtracting the oldest odometer - earlier odometer and oldest pick up time - earlier pick up time for each group. Don't know how to also get a distinct count of clientid for each group.

Does that make sense?

drew.allen SSC Guru Points: 76985 More actions · Answer 3

I see nothing in your data that indicates that you picked up 9 people at the same time/location. There is no way to provide a solution without that field. If you have such a field, you would need to add it to the PARTITION BY clause and change the ROWS UNBOUNDED PRECEDING to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This is close.

SELECT *, SUM(CASE WHEN t.Event_Activity = 'Pick' THEN 1 WHEN t.Event_Activity = 'Drop' THEN -1 END) OVER(PARTITION BY Route ORDER BY t.LDate, t.EvOrder ROWS UNBOUNDED PRECEDING) AS PassengerCount
FROM #tripinfo AS t
ORDER BY Route, t.LDate, t.EvOrder

This is also almost exactly what I provided in your earlier thread. I even labelled the field CurrentPassengerCount.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76985 More actions · Answer 4

When you provide sample data DO NOT USE A PERMANENT TABLE. You should either provide a temp table or a table variable.

When you provide sample data, the VALUES clause can take MORE THAN ONE ROW. In fact, it can take up to 1000 rows. There is absolutely no reason to have a separate insert statement for EVERY SINGLE ROW.

Your date data should be stored as a DATE.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

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

Apologies on the permanent table. I dumped the data I had into a permanent table so I could use Generate Scripts to script out the table schema and data at once. That's why the scripts for the data look the way they do. I neglected to go back and change the table name to a temp table, so my apologies on that.

The query you provided appears to be doing something like a running count of passengers on each event but it doesn't quite get it right. I need just one number for all riders,each time riders are on the vehicle. If you look at the second round of sample data I posted above, it illustrates the expected result with a single number indicating the number of distinct riders in each "cluster" or group. (This specific format is so that I can tie this passenger count back to other data in that table to tell me how many of those passengers are ambulatory or not).

The Event_Activity shows we pick up a passenger (or drop off). The ClientID indicates the passenger. The PassOB field is similar in that it shows who is getting on the vehicle and already does a running count of passengers that are currently onboard. Another way of looking at this is that if I could get the MAX(PassOB#) for EACH grouping, that would theoretically be the number I'm looking for. And if that number would be in a separate column on the first event of each grouping, that's the ideal placement. (This is what I listed in the ClientCount column).

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

I'll add that the final expected output doesn't necessarily have to include all those columns. The minimum would be date, EvstrName, RN, and PassCount (for each grouping). The final result of the previous thread on this provided the right format. With those fields listed above, I could join the results back to the full table of data.

While I don't want to muddy the waters, in order to provide more background, the previous query that provided some similar (but different) results I was originally looking for (in the previous thread mentioned) is as follows:

SELECT LDATE
,EVSTRNAME
,RN
,LEAD(RN,1,0) OVER (ORDER BY LDATE,EVSTRNAME,RN) LeadRN
,PASSOB
,[TIME] TravelMinutes
,CAST(   ROUND(MILES,0) AS INT) Miles
,'Pick' Activity
FROM 
(SELECT LDATE
,EVSTRNAME
,RN
,LEAD(RN,1,0) OVER (ORDER BY LDATE,EVSTRNAME,RN) - RN AS [PASSOB]
,LEAD(  (ACTUALARRIVETIME/60)  ,1,0) OVER (ORDER BY LDATE,EVSTRNAME,RN) - (ACTUALDEPARTTIME/60) AS [TIME]
,LEAD(ODOMETER,1,0) OVER (ORDER BY LDATE,EVSTRNAME,RN)-ODOMETER AS [MILES]
FROM 
(SELECT LDATE, EVSTRNAME,RN,ACTUALARRIVETIME,ACTUALDEPARTTIME,ODOMETERFROM #temp
WHERE passob_bit = 0 OR RN = 1
UNION ALL
SELECT B.LDATE,b.EVSTRNAME,b.RN,b.ACTUALARRIVETIME,b.ACTUALDEPARTTIME,b.ODOMETER
FROM #temp a
JOIN #temp b ON b.LDATE = A.LDATE AND b.EVSTRNAME = a.EVSTRNAME AND b.RN = a.RN+1
WHERE a.passob_bit = 0) c
) d
WHERE RN % 2 = 1
ORDER BY LDATE,EVSTRNAME

It references a few other columns I didn't include here since they're not relevant to just a count of passengers. But it's showing for each grouping of passengers, what is the RN, LeadRN, PassOB (how many events are there where more than 1 person is onboard together) in a grouping, plus Mins, Miles, and activity.

This is the data/format that query returns (for the same sample data I provided above):

CREATE TABLE #test(
[LDATE] [int] NULL,
[EVSTRNAME] [varchar](20) NULL,
[RN] [bigint] NULL,
[LeadRN] [bigint] NULL,
[PASSOB] [bigint] NULL,
[TravelMinutes] [int] NULL,
[Miles] [int] NULL,
[Activity] [varchar](4) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'100', 1, 19, 17, 91, 29, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'100', 19, 21, 1, 17, 4, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'100', 21, 23, 1, 32, 22, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'100', 23, 1, 1, 15, 5, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'101A', 1, 3, 1, 5, 1, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'101A', 3, 7, 3, 65, 20, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'101A', 7, 1, 1, 21, 16, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'102', 1, 5, 3, 35, 15, N'Pick')
GO
INSERT #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'102', 5, 7, 1, 24, 18, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'102', 7, 13, 5, 64, 27, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'102', 13, 1, 3, 69, 29, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'103A', 1, 3, 1, 14, 4, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'103A', 3, 5, 1, 23, 9, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'103A', 5, 7, 1, 26, 11, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'103A', 7, 9, 1, 23, 10, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'103A', 9, 1, 1, 17, 10, N'Pick')
GO

I added those results back to the rest of the table data to get this "final result" (of the other fields I was looking for):

CREATE TABLE #final (
[LDate] [int] NULL,
[TripDate] [char](10) NULL,
[DOW] [varchar](7) NOT NULL,
[RN] [bigint] NULL,
[BookingID] [int] NULL,
[ClientID] [int] NULL,
[Evstrname] [varchar](20) NULL,
[Event_Activity] [varchar](4) NULL,
[EvOrder] [int] NULL,
[AAT] [char](5) NULL,
[ADT] [char](5) NULL,
[Odometer] [numeric](23, 12) NULL,
[PassOB] [varchar](50) NULL,
[passOB_bit] [int] NOT NULL,
[SpaceOB] [varchar](50) NULL,
[PassOBCount] [bigint] NULL,
[TravelMinutes] [int] NULL,
[Miles] [int] NULL
) ON [PRIMARY]
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 1, 9977026, 65250, N'100', N'Pick', 16666698, N'14:12', N'14:18', CAST(102813.968260857256 AS Numeric(23, 12)), N'CLI1', 1, N'AM1', 17, 91, 29)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 2, 9977447, 75030, N'100', N'Pick', 30952407, N'14:12', N'14:18', CAST(102813.968260857256 AS Numeric(23, 12)), N'CLI2', 1, N'AM2', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 3, 9977827, 55275, N'100', N'Pick', 33809569, N'14:12', N'14:18', CAST(102813.968260857256 AS Numeric(23, 12)), N'CLI3', 1, N'AM3', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 4, 9976922, 22729, N'100', N'Pick', 43452396, N'14:12', N'14:18', CAST(102813.968260857256 AS Numeric(23, 12)), N'CLI4', 1, N'AM4', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 5, 9976996, 26192, N'100', N'Pick', 54252207, N'14:12', N'14:18', CAST(102813.968260857256 AS Numeric(23, 12)), N'CLI5', 1, N'AM5', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 6, 9976998, 50205, N'100', N'Pick', 62352047, N'14:12', N'14:18', CAST(102813.968260857256 AS Numeric(23, 12)), N'CLI6', 1, N'AM6', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 7, 9977104, 71409, N'100', N'Pick', 72983124, N'14:12', N'14:18', CAST(102813.968260857256 AS Numeric(23, 12)), N'CLI7', 1, N'AM7', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 8, 9977108, 52806, N'100', N'Pick', 76400231, N'14:12', N'14:18', CAST(102813.968260857256 AS Numeric(23, 12)), N'CLI8', 1, N'AM8', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 9, 9977755, 39327, N'100', N'Pick', 78963106, N'14:12', N'14:18', CAST(102813.968260857256 AS Numeric(23, 12)), N'CLI9', 1, N'AM9', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 10, 9977026, 65250, N'100', N'Drop', 80885231, N'14:34', N'14:38', CAST(102818.345199533704 AS Numeric(23, 12)), N'CLI8', 1, N'AM8', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 11, 9976922, 22729, N'100', N'Drop', 82326807, N'15:00', N'15:01', CAST(102829.118533260600 AS Numeric(23, 12)), N'CLI7', 1, N'AM7', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 12, 9977827, 55275, N'100', N'Drop', 83408020, N'15:19', N'15:20', CAST(102836.944703423840 AS Numeric(23, 12)), N'CLI6', 1, N'AM6', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 13, 9976996, 26192, N'100', N'Drop', 84218893, N'15:24', N'15:24', CAST(102837.895401347600 AS Numeric(23, 12)), N'CLI5', 1, N'AM5', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 14, 9977104, 71409, N'100', N'Drop', 84827057, N'15:28', N'15:28', CAST(102838.769670614744 AS Numeric(23, 12)), N'CLI4', 1, N'AM4', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 15, 9977108, 52806, N'100', N'Drop', 85283198, N'15:28', N'15:28', CAST(102838.769670614744 AS Numeric(23, 12)), N'CLI3', 1, N'AM3', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 16, 9977755, 39327, N'100', N'Drop', 85625317, N'15:32', N'15:32', CAST(102839.126337678952 AS Numeric(23, 12)), N'CLI2', 1, N'AM2', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 17, 9976998, 50205, N'100', N'Drop', 85881871, N'15:43', N'15:43', CAST(102841.808175743624 AS Numeric(23, 12)), N'CLI1', 1, N'AM1', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 18, 9977447, 75030, N'100', N'Drop', 86074327, N'15:49', N'15:49', CAST(102843.226144803768 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 19, 9977888, 72467, N'100', N'Pick', 96810358, N'19:37', N'19:41', CAST(102869.941377832616 AS Numeric(23, 12)), N'CLI1', 1, N'XW1', 1, 17, 4)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 20, 9977888, 72467, N'100', N'Drop', 97013805, N'19:58', N'19:58', CAST(102874.074739001800 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 21, 9986778, 46029, N'100', N'Pick', 97069755, N'20:32', N'20:32', CAST(102897.560705945824 AS Numeric(23, 12)), N'CLI1', 1, N'AM1', 1, 32, 22)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 22, 9986778, 46029, N'100', N'Drop', 97100574, N'21:04', N'21:05', CAST(102919.857989799552 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 23, 9987355, 71459, N'100', N'Pick', 97445354, N'21:19', N'21:22', CAST(102925.082478781888 AS Numeric(23, 12)), N'CLI1', 1, N'LR1', 1, 15, 5)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 24, 9987355, 71459, N'100', N'Drop', 97638581, N'21:37', N'21:37', CAST(102930.293297598000 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 1, 9977487, 49445, N'101A', N'Pick', 39062527, N'09:11', N'09:27', CAST(28127.999969372576 AS Numeric(23, 12)), N'CLI1', 1, N'WH1', 1, 5, 1)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 2, 9977487, 49445, N'101A', N'Drop', 40234399, N'09:32', N'09:37', CAST(28128.999755620504 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 3, 9977202, 60716, N'101A', N'Pick', 41113303, N'09:48', N'09:58', CAST(28130.000163239624 AS Numeric(23, 12)), N'CLI1', 1, N'WH1', 3, 65, 20)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 4, 9977087, 8199, N'101A', N'Pick', 41772496, N'10:15', N'10:26', CAST(28136.000123469576 AS Numeric(23, 12)), N'CLI2', 1, N'LR1,WH1', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 5, 9977202, 60716, N'101A', N'Drop', 42266870, N'10:38', N'10:43', CAST(28139.999889832480 AS Numeric(23, 12)), N'CLI1', 1, N'LR1', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 6, 9977087, 8199, N'101A', N'Drop', 42637652, N'11:03', N'11:07', CAST(28150.000237796528 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 7, 9986963, 78437, N'101A', N'Pick', 43471931, N'11:41', N'11:44', CAST(28165.999924619336 AS Numeric(23, 12)), N'CLI1', 1, N'LR1', 1, 21, 16)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 8, 9986963, 78437, N'101A', N'Drop', 43680478, N'12:05', N'12:07', CAST(28182.000232813336 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 1, 9977615, 5152, N'102', N'Pick', 25000028, N'10:12', N'10:28', CAST(22477.615094214280 AS Numeric(23, 12)), N'CLI1,PCA1', 1, N'AM1,WH1', 3, 35, 15)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 2, 9977480, 61043, N'102', N'Pick', 43750026, N'10:28', N'10:29', CAST(22479.199590753880 AS Numeric(23, 12)), N'CLI2,PCA1', 1, N'AM2,WH1', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 3, 9977480, 61043, N'102', N'Drop', 57812527, N'10:42', N'10:43', CAST(22482.963236063824 AS Numeric(23, 12)), N'CLI1,PCA1', 1, N'AM1,WH1', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 4, 9977615, 5152, N'102', N'Drop', 68359403, N'11:03', N'11:06', CAST(22492.942457407344 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 5, 9984724, 83955, N'102', N'Pick', 68979778, N'11:33', N'11:41', CAST(22501.347124150336 AS Numeric(23, 12)), N'CLI1', 1, N'XW1', 1, 24, 18)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 6, 9984724, 83955, N'102', N'Drop', 69425423, N'12:05', N'12:16', CAST(22519.066145061408 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 7, 9984906, 58263, N'102', N'Pick', 82549513, N'13:35', N'13:38', CAST(22529.820216281352 AS Numeric(23, 12)), N'CLI1', 1, N'AM1', 5, 64, 27)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 8, 9987359, 74845, N'102', N'Pick', 83575045, N'13:56', N'13:58', CAST(22539.017752665336 AS Numeric(23, 12)), N'CLI2', 1, N'AM2', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 9, 9977254, 68974, N'102', N'Pick', 86651643, N'13:56', N'13:58', CAST(22539.017752665336 AS Numeric(23, 12)), N'CLI3', 1, N'AM3', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 10, 9987359, 74845, N'102', N'Drop', 87485886, N'14:17', N'14:18', CAST(22548.164957982768 AS Numeric(23, 12)), N'CLI2', 1, N'AM2', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 11, 9977254, 68974, N'102', N'Drop', 89988737, N'14:25', N'14:26', CAST(22549.933380395200 AS Numeric(23, 12)), N'CLI1', 1, N'AM1', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 12, 9984906, 58263, N'102', N'Drop', 93312775, N'14:42', N'14:44', CAST(22557.263074976032 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 13, 9977770, 82886, N'102', N'Pick', 93928726, N'15:08', N'15:19', CAST(22570.072020727920 AS Numeric(23, 12)), N'CLI1', 1, N'WH1', 3, 69, 29)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 14, 9984747, 46670, N'102', N'Pick', 97178839, N'15:39', N'15:48', CAST(22582.140292018944 AS Numeric(23, 12)), N'CLI2', 1, N'WH2', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 15, 9977770, 82886, N'102', N'Drop', 98218219, N'16:10', N'16:14', CAST(22594.099823351368 AS Numeric(23, 12)), N'CLI1', 1, N'WH1', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 16, 9984747, 46670, N'102', N'Drop', 98475756, N'16:28', N'16:32', CAST(22599.027918275120 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 1, 9987615, 621, N'103A', N'Pick', 22309558, N'07:35', N'07:40', CAST(184282.561644199720 AS Numeric(23, 12)), N'CLI1', 1, N'LR1', 1, 14, 4)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 2, 9987615, 621, N'103A', N'Drop', 26500032, N'07:54', N'07:57', CAST(184286.636596476856 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 3, 9984941, 83926, N'103A', N'Pick', 39071390, N'08:15', N'08:17', CAST(184291.631178118152 AS Numeric(23, 12)), N'CLI1', 1, N'LR1', 1, 23, 9)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 4, 9984941, 83926, N'103A', N'Drop', 43551572, N'08:40', N'08:40', CAST(184300.196779999872 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 5, 9987385, 62003, N'103A', N'Pick', 56992092, N'08:58', N'08:58', CAST(184304.132545130000 AS Numeric(23, 12)), N'CLI1', 1, N'LR1', 1, 26, 11)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 6, 9987385, 62003, N'103A', N'Drop', 58869436, N'09:24', N'09:26', CAST(184315.384956045928 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 7, 9987141, 65507, N'103A', N'Pick', 64501469, N'09:39', N'09:43', CAST(184319.231243724408 AS Numeric(23, 12)), N'CLI1', 1, N'WH1', 1, 23, 10)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 8, 9987141, 65507, N'103A', N'Drop', 72648606, N'10:06', N'10:09', CAST(184329.435401439432 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 9, 9988781, 79977, N'103A', N'Pick', 75364314, N'10:37', N'10:41', CAST(184341.371942037752 AS Numeric(23, 12)), N'CLI1,PCA1', 1, N'AM1,WH1', 1, 17, 10)
GO
INSERT into #FINAL ([LDate], [TripDate], [DOW], [RN], [BookingID], [ClientID], [Evstrname], [Event_Activity], [EvOrder], [AAT], [ADT], [Odometer], [PassOB], [passOB_bit], [SpaceOB], [PassOBCount], [TravelMinutes], [Miles]) VALUES (20200114, N'01/14/2020', N'WEEKDAY', 10, 9988781, 79977, N'103A', N'Drop', 76043233, N'10:58', N'11:02', CAST(184351.157295569368 AS Numeric(23, 12)), N'', 0, N'', NULL, NULL, NULL)
GO

If we could add a column called ClientCount, similar to PassOBCount here, that would be the ticket.

I hope I'm not further confusing things here. Just trying to provide as much info as possible.

*had to make some further edits to the sample data as in changing the table name to a temp table I messed it up. It's good now.

This reply was modified 4 years, 10 months ago by tacy.highland.
This reply was modified 4 years, 10 months ago by tacy.highland.

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

tacy.highland wrote:

The query you provided appears to be doing something like a running count of passengers on each event but it doesn't quite get it right. I need just one number for all riders,each time riders are on the vehicle. If you look at the second round of sample data I posted above, it illustrates the expected result with a single number indicating the number of distinct riders in each "cluster" or group. (This specific format is so that I can tie this passenger count back to other data in that table to tell me how many of those passengers are ambulatory or not).

When I posted the query, I specifically said that what you wanted was impossible with the data provided to that point and also pointed you in the direction of how to solve this problem once the required fields were provided. You now have enough information to figure this out on your own with a little experimentation. You're never going to learn if you don't understand how the code works, and you're never going to understand how the code works if you don't experiment. Try to figure this out on your own and post your results here.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

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

I have spent several hours/days trying to figure this out with no luck, which is why I posted the question here. I may not be looking at it the right way or just don't know how to adapt the code I have to get what I'm looking for. I'm just looking for some help here and while I'm decent, I'm not the most proficient query writer out there.

I was originally trying to adapt the query to use the RN and LeadRN columns to try to retrieve a list of the ClientIDs between those two numbers so I could do a distinct count but I couldn't get that to work. Using the #tripinfo and the #test tables of sample data below, I have a query that is close but it's not correct:

CREATE TABLE #tripinfo(
[LDate] [int] NULL,
[TripDate] [char](10) NULL,
[RN] [bigint] NULL,
[BookingID] [int] NULL,
[Route] [varchar](20) NULL,
[Event_Activity] [varchar](4) NULL,
[EvOrder] [int] NULL,
[ClientID] [int] NULL,
[PassOB] [varchar](50) NULL,
[SpaceOB] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 1, 9977026, N'100', N'Pick', 16666698, 65250, N'CLI1', N'AM1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 2, 9977447, N'100', N'Pick', 30952407, 75030, N'CLI2', N'AM2')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 3, 9977827, N'100', N'Pick', 33809569, 55275, N'CLI3', N'AM3')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 4, 9976922, N'100', N'Pick', 43452396, 22729, N'CLI4', N'AM4')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 5, 9976996, N'100', N'Pick', 54252207, 26192, N'CLI5', N'AM5')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 6, 9976998, N'100', N'Pick', 62352047, 50205, N'CLI6', N'AM6')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 7, 9977104, N'100', N'Pick', 72983124, 71409, N'CLI7', N'AM7')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 8, 9977108, N'100', N'Pick', 76400231, 52806, N'CLI8', N'AM8')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 9, 9977755, N'100', N'Pick', 78963106, 39327, N'CLI9', N'AM9')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 10, 9977026, N'100', N'Drop', 80885231, 65250, N'CLI8', N'AM8')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 11, 9976922, N'100', N'Drop', 82326807, 22729, N'CLI7', N'AM7')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 12, 9977827, N'100', N'Drop', 83408020, 55275, N'CLI6', N'AM6')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 13, 9976996, N'100', N'Drop', 84218893, 26192, N'CLI5', N'AM5')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 14, 9977104, N'100', N'Drop', 84827057, 71409, N'CLI4', N'AM4')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 15, 9977108, N'100', N'Drop', 85283198, 52806, N'CLI3', N'AM3')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 16, 9977755, N'100', N'Drop', 85625317, 39327, N'CLI2', N'AM2')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 17, 9976998, N'100', N'Drop', 85881871, 50205, N'CLI1', N'AM1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 18, 9977447, N'100', N'Drop', 86074327, 75030, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 19, 9977888, N'100', N'Pick', 96810358, 72467, N'CLI1', N'XW1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 20, 9977888, N'100', N'Drop', 97013805, 72467, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 21, 9986778, N'100', N'Pick', 97069755, 46029, N'CLI1', N'AM1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 22, 9986778, N'100', N'Drop', 97100574, 46029, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 23, 9987355, N'100', N'Pick', 97445354, 71459, N'CLI1', N'LR1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 24, 9987355, N'100', N'Drop', 97638581, 71459, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 1, 9977487, N'101A', N'Pick', 39062527, 49445, N'CLI1', N'WH1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 2, 9977487, N'101A', N'Drop', 40234399, 49445, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 3, 9977202, N'101A', N'Pick', 41113303, 60716, N'CLI1', N'WH1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 4, 9977087, N'101A', N'Pick', 41772496, 8199, N'CLI2', N'LR1,WH1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 5, 9977202, N'101A', N'Drop', 42266870, 60716, N'CLI1', N'LR1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 6, 9977087, N'101A', N'Drop', 42637652, 8199, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 7, 9986963, N'101A', N'Pick', 43471931, 78437, N'CLI1', N'LR1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 8, 9986963, N'101A', N'Drop', 43680478, 78437, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 1, 9977615, N'102', N'Pick', 25000028, 5152, N'CLI1,PCA1', N'AM1,WH1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 2, 9977480, N'102', N'Pick', 43750026, 61043, N'CLI2,PCA1', N'AM2,WH1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 3, 9977480, N'102', N'Drop', 57812527, 61043, N'CLI1,PCA1', N'AM1,WH1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 4, 9977615, N'102', N'Drop', 68359403, 5152, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 5, 9984724, N'102', N'Pick', 68979778, 83955, N'CLI1', N'XW1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 6, 9984724, N'102', N'Drop', 69425423, 83955, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 7, 9984906, N'102', N'Pick', 82549513, 58263, N'CLI1', N'AM1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 8, 9987359, N'102', N'Pick', 83575045, 74845, N'CLI2', N'AM2')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 9, 9977254, N'102', N'Pick', 86651643, 68974, N'CLI3', N'AM3')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 10, 9987359, N'102', N'Drop', 87485886, 74845, N'CLI2', N'AM2')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 11, 9977254, N'102', N'Drop', 89988737, 68974, N'CLI1', N'AM1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 12, 9984906, N'102', N'Drop', 93312775, 58263, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 13, 9977770, N'102', N'Pick', 93928726, 82886, N'CLI1', N'WH1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 14, 9984747, N'102', N'Pick', 97178839, 46670, N'CLI2', N'WH2')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 15, 9977770, N'102', N'Drop', 98218219, 82886, N'CLI1', N'WH1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 16, 9984747, N'102', N'Drop', 98475756, 46670, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 1, 9987615, N'103A', N'Pick', 22309558, 621, N'CLI1', N'LR1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 2, 9987615, N'103A', N'Drop', 26500032, 621, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 3, 9984941, N'103A', N'Pick', 39071390, 83926, N'CLI1', N'LR1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 4, 9984941, N'103A', N'Drop', 43551572, 83926, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 5, 9987385, N'103A', N'Pick', 56992092, 62003, N'CLI1', N'LR1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 6, 9987385, N'103A', N'Drop', 58869436, 62003, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 7, 9987141, N'103A', N'Pick', 64501469, 65507, N'CLI1', N'WH1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 8, 9987141, N'103A', N'Drop', 72648606, 65507, N'', N'')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 9, 9988781, N'103A', N'Pick', 75364314, 79977, N'CLI1,PCA1', N'AM1,WH1')
GO
INSERT into #tripinfo ([LDate], [TripDate], [RN], [BookingID], [Route], [Event_Activity], [EvOrder], [ClientID], [PassOB], [SpaceOB]) VALUES (20200114, N'01/14/2020', 10, 9988781, N'103A', N'Drop', 76043233, 79977, N'', N'')
GO



CREATE TABLE #test(
[LDATE] [int] NULL,
[EVSTRNAME] [varchar](20) NULL,
[RN] [bigint] NULL,
[LeadRN] [bigint] NULL,
[PASSOB] [bigint] NULL,
[TravelMinutes] [int] NULL,
[Miles] [int] NULL,
[Activity] [varchar](4) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'100', 1, 19, 17, 91, 29, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'100', 19, 21, 1, 17, 4, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'100', 21, 23, 1, 32, 22, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'100', 23, 1, 1, 15, 5, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'101A', 1, 3, 1, 5, 1, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'101A', 3, 7, 3, 65, 20, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'101A', 7, 1, 1, 21, 16, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'102', 1, 5, 3, 35, 15, N'Pick')
GO
INSERT #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'102', 5, 7, 1, 24, 18, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'102', 7, 13, 5, 64, 27, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'102', 13, 1, 3, 69, 29, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'103A', 1, 3, 1, 14, 4, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'103A', 3, 5, 1, 23, 9, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'103A', 5, 7, 1, 26, 11, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'103A', 7, 9, 1, 23, 10, N'Pick')
GO
INSERT INTO  #test ([LDATE], [EVSTRNAME], [RN], [LeadRN], [PASSOB], [TravelMinutes], [Miles], [Activity]) VALUES (20200114, N'103A', 9, 1, 1, 17, 10, N'Pick')
GO

--select * from #tripinfo
--select * from #test

SELECT T.[route]
,f.RN
--,f.LeadRN
,COUNT(T.ClientID)  CLIENTS
FROM #tripinfo t 
LEFT JOIN #test f ON f.ldate = t.ldate and f.evstrname = t.[route] and f.rn = t.rn and t.Event_Activity = f.Activity AND t.RN BETWEEN f.RN AND f.LeadRN
WHERE Event_Activity = 'Pick' 
GROUP BY T.[route],F.RN,F.LeadRN

DROP TABLE #test,#tripinfo

On this query's results, I don't know why the RN field is blank on the first record for each route and why it's not counting the clients correctly for each grouping. Route 100 looks correct with 9,1,1,1 but route 101A is wrong starting off with 2,1,1 when it should be 1,2,1, and the remaining routes are wrong as well. Clearly my query is not right.

If you don't want to offer any further assistance, that's ok. I appreciate you taking the time that you have.

Hopefully someone else can also offer some more ideas on how to tackle this.

Thanks