March 21, 2020 at 11:47 pm
Hello all,
I posted something similar to this not long ago but haven't gotten any bites so I thought I'd try again.
I have bus manifest data, picks and drops, for multiple routes on multiple days. I'm trying to get a running total of how many riders are on the bus each time someone is onboard the vehicle. I have some code but it's not quite right. Hoping someone can offer some pointers on how to fix it.
Test data with 2 routes/buses:
CREATE TABLE #Manifest(
[TripDateISO] [int] NULL,
[RN] [bigint] NULL,
[Route] [varchar](255) NULL,
[StopType] [varchar](1) NOT NULL,
[TripID] [int] NULL,
[CustomerID] [int] NULL,
[StopDateTime] [datetime] NULL,
[Address] [varchar](511) NULL,
[PassengerStatusDescription] [varchar](9) NOT NULL,
[OnOff] [int] NOT NULL,
[passOB_bit] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 1, N'902', N'P', 473378, 7416, CAST(N'2020-03-12 13:11:45.000' AS DateTime), N'412 S Raymond Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 2, N'902', N'D', 473378, 7416, CAST(N'2020-03-12 13:23:13.000' AS DateTime), N'1855 N Fair Oaks ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 3, N'902', N'P', 473431, 7674, CAST(N'2020-03-12 14:04:57.000' AS DateTime), N'285 E Walnut ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 4, N'902', N'D', 473431, 7674, CAST(N'2020-03-12 14:13:23.000' AS DateTime), N'427 N Craig Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 5, N'902', N'P', 473292, 3372, CAST(N'2020-03-12 14:22:03.000' AS DateTime), N'3280 E Foothill Blvd ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 6, N'902', N'D', 473292, 3372, CAST(N'2020-03-12 14:38:05.000' AS DateTime), N'2133 Raymond Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 7, N'902', N'P', 464846, 1747, CAST(N'2020-03-12 14:55:48.000' AS DateTime), N'1081 N Fair Oaks Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 8, N'902', N'D', 464846, 1747, CAST(N'2020-03-12 15:02:28.000' AS DateTime), N'1657 N Los Robles Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 9, N'902', N'P', 473644, 7299, CAST(N'2020-03-12 16:02:58.000' AS DateTime), N'1224 E Green St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 10, N'902', N'D', 473644, 7299, CAST(N'2020-03-12 16:13:27.000' AS DateTime), N'811 E Washington Blvd #24', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 11, N'902', N'P', 473331, 7582, CAST(N'2020-03-12 16:55:53.000' AS DateTime), N'85 E. Holly Street ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 12, N'902', N'P', 473285, 5589, CAST(N'2020-03-12 17:17:33.000' AS DateTime), N'648 N Wilson Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 13, N'902', N'D', 473331, 7582, CAST(N'2020-03-12 17:25:02.000' AS DateTime), N'62 Marion Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 14, N'902', N'D', 473285, 5589, CAST(N'2020-03-12 17:36:20.000' AS DateTime), N'3325 E Orange Grove ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 15, N'902', N'P', 473286, 5589, CAST(N'2020-03-12 18:14:38.000' AS DateTime), N'3325 E Orange Grove ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 16, N'902', N'D', 473286, 5589, CAST(N'2020-03-12 18:24:58.000' AS DateTime), N'648 N Wilson Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 1, N'903', N'P', 473253, 6399, CAST(N'2020-03-12 07:05:48.000' AS DateTime), N'170 N GRAND AVE #306', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 2, N'903', N'D', 473253, 6399, CAST(N'2020-03-12 07:13:18.000' AS DateTime), N'2180 Lincoln Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 3, N'903', N'P', 377228, 4746, CAST(N'2020-03-12 07:19:55.000' AS DateTime), N'1299 N Los Robles Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 4, N'903', N'D', 377228, 4746, CAST(N'2020-03-12 07:30:37.000' AS DateTime), N' 2551 E Washington ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 5, N'903', N'P', 398225, 3637, CAST(N'2020-03-12 08:18:36.000' AS DateTime), N'1385 N Mentor Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 6, N'903', N'P', 398226, 3639, CAST(N'2020-03-12 08:23:14.000' AS DateTime), N'916 N Chester Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 7, N'903', N'P', 437580, 247, CAST(N'2020-03-12 08:29:20.000' AS DateTime), N'1260 N El Molino Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 8, N'903', N'P', 421454, 3638, CAST(N'2020-03-12 08:40:35.000' AS DateTime), N'872 N Raymond Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 9, N'903', N'D', 398225, 3637, CAST(N'2020-03-12 08:47:52.000' AS DateTime), N'412 w Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 10, N'903', N'D', 398226, 3639, CAST(N'2020-03-12 08:47:55.000' AS DateTime), N'412 w Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 11, N'903', N'D', 437580, 247, CAST(N'2020-03-12 08:47:57.000' AS DateTime), N'412 w Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 12, N'903', N'D', 421454, 3638, CAST(N'2020-03-12 08:48:03.000' AS DateTime), N'412 w Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 13, N'903', N'P', 446949, 235, CAST(N'2020-03-12 08:54:04.000' AS DateTime), N'1852 Newport ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 14, N'903', N'P', 395966, 262, CAST(N'2020-03-12 08:56:27.000' AS DateTime), N'1980 Mentone ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 15, N'903', N'P', 395970, 3933, CAST(N'2020-03-12 09:03:03.000' AS DateTime), N'310 W Altadena Dr ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 16, N'903', N'P', 395964, 5533, CAST(N'2020-03-12 09:05:06.000' AS DateTime), N'24 W Harriet St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 17, N'903', N'P', 395960, 5475, CAST(N'2020-03-12 09:05:15.000' AS DateTime), N'24 W Harriet St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 18, N'903', N'P', 395961, 5474, CAST(N'2020-03-12 09:05:22.000' AS DateTime), N'24 W Harriet St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 19, N'903', N'P', 395967, 4803, CAST(N'2020-03-12 09:12:46.000' AS DateTime), N'1760 Fair Oaks Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 20, N'903', N'P', 395968, 5116, CAST(N'2020-03-12 09:12:54.000' AS DateTime), N'1760 N Fair Oaks Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 21, N'903', N'D', 446949, 235, CAST(N'2020-03-12 09:18:59.000' AS DateTime), N'412 w Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 22, N'903', N'D', 395966, 262, CAST(N'2020-03-12 09:19:00.000' AS DateTime), N'412 w Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 23, N'903', N'D', 395970, 3933, CAST(N'2020-03-12 09:19:01.000' AS DateTime), N'421 Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 24, N'903', N'D', 395964, 5533, CAST(N'2020-03-12 09:19:02.000' AS DateTime), N'412 w Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 25, N'903', N'D', 395960, 5475, CAST(N'2020-03-12 09:19:03.000' AS DateTime), N'412 w Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 26, N'903', N'D', 395961, 5474, CAST(N'2020-03-12 09:19:04.000' AS DateTime), N'412 w Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 27, N'903', N'D', 395967, 4803, CAST(N'2020-03-12 09:19:07.000' AS DateTime), N'412 w Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 28, N'903', N'D', 395968, 5116, CAST(N'2020-03-12 09:19:09.000' AS DateTime), N'412 w Del Monte St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 29, N'903', N'P', 473596, 7675, CAST(N'2020-03-12 10:07:34.000' AS DateTime), N'2144 Brigden Rd ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 30, N'903', N'D', 473596, 7675, CAST(N'2020-03-12 10:15:28.000' AS DateTime), N'1300 N Lake Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 31, N'903', N'P', 473297, 3120, CAST(N'2020-03-12 11:56:24.000' AS DateTime), N'630 S Raymond Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 32, N'903', N'D', 473297, 3120, CAST(N'2020-03-12 12:04:57.000' AS DateTime), N'378 Laun St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 33, N'903', N'P', 473426, 7098, CAST(N'2020-03-12 12:53:28.000' AS DateTime), N'2029 Lincoln Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 34, N'903', N'D', 473426, 7098, CAST(N'2020-03-12 13:00:51.000' AS DateTime), N'154 Painter St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 35, N'903', N'P', 473642, 7299, CAST(N'2020-03-12 13:41:08.000' AS DateTime), N'811 E Washington Blvd #24', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 36, N'903', N'D', 473642, 7299, CAST(N'2020-03-12 13:54:04.000' AS DateTime), N'1224 E Green St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 37, N'903', N'P', 380946, 5475, CAST(N'2020-03-12 14:29:37.000' AS DateTime), N'412 w Del Monte St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 38, N'903', N'P', 380949, 5474, CAST(N'2020-03-12 14:29:49.000' AS DateTime), N'412 w Del Monte St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 39, N'903', N'P', 380950, 5533, CAST(N'2020-03-12 14:30:00.000' AS DateTime), N'412 w Del Monte St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 40, N'903', N'P', 380952, 4803, CAST(N'2020-03-12 14:30:23.000' AS DateTime), N'412 w Del Monte St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 41, N'903', N'P', 380954, 5116, CAST(N'2020-03-12 14:30:36.000' AS DateTime), N'412 w Del Monte St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 42, N'903', N'P', 454791, 247, CAST(N'2020-03-12 14:30:54.000' AS DateTime), N'412 w Del Monte St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 43, N'903', N'P', 454799, 3639, CAST(N'2020-03-12 14:31:04.000' AS DateTime), N'412 w Del Monte St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 44, N'903', N'P', 454802, 3637, CAST(N'2020-03-12 14:31:34.000' AS DateTime), N'412 w Del Monte St ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 45, N'903', N'D', 380946, 5475, CAST(N'2020-03-12 14:39:30.000' AS DateTime), N'24 W Harriet St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 46, N'903', N'D', 380949, 5474, CAST(N'2020-03-12 14:39:32.000' AS DateTime), N'24 W Harriet St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 47, N'903', N'D', 380950, 5533, CAST(N'2020-03-12 14:39:36.000' AS DateTime), N'24 W Harriet St ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 48, N'903', N'D', 454802, 3637, CAST(N'2020-03-12 14:47:32.000' AS DateTime), N'1385 N Mentor Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 49, N'903', N'D', 454799, 3639, CAST(N'2020-03-12 14:52:07.000' AS DateTime), N'916 N Chester Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 50, N'903', N'D', 454791, 247, CAST(N'2020-03-12 14:57:59.000' AS DateTime), N'1260 N El Molino Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 51, N'903', N'D', 380952, 4803, CAST(N'2020-03-12 15:04:06.000' AS DateTime), N'1760 Fair Oaks Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 52, N'903', N'D', 380954, 5116, CAST(N'2020-03-12 15:04:09.000' AS DateTime), N'1760 N Fair Oaks Ave ', N'Disembark', -1, 0)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 53, N'903', N'P', 473476, 7087, CAST(N'2020-03-12 15:22:48.000' AS DateTime), N'625 S Fair Oaks Ave ', N'Board', 1, 1)
GO
INSERT #Manifest ([TripDateISO], [RN], [Route], [StopType], [TripID], [CustomerID], [StopDateTime], [Address], [PassengerStatusDescription], [OnOff], [passOB_bit]) VALUES (20200312, 54, N'903', N'D', 473476, 7087, CAST(N'2020-03-12 15:37:15.000' AS DateTime), N'86 W Mountain View St ', N'Disembark', -1, 0)
GO
I'm trying to get a count of the passengers onboard, each time someone is onboard. Expected results would look like this:
CREATE TABLE #Expected(
[TripDateISO] [int] NULL,
[Route] [varchar](255) NULL,
[RN] [bigint] NULL,
[PASSOB] [bigint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'902', 1, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'902', 3, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'902', 5, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'902', 7, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'902', 9, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'902', 11, 2)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'902', 15, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'903', 1, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'903', 3, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'903', 5, 4)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'903', 13, 8)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'903', 29, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'903', 31, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'903', 33, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'903', 35, 1)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'903', 37, 8)
GO
INSERT #Expected ([TripDateISO], [Route], [RN], [PASSOB]) VALUES (20200312, N'903', 53, 1)
GO
The code I tried is from another great forum person out there, and while it's close, it's not quite getting what I'm hoping for:
SELECT[Route],RN,PASSOB
FROM(
SELECT[Route],RN,
LEAD(RN,1,0) OVER (ORDER BY [Route],RN)-RN AS [PASSOB]
FROM(
SELECT[Route],RN
FROM#manifest
WHEREpassob_bit = 0 OR RN = 1
UNION ALL
SELECTb.[Route],b.RN
FROM# manifest a
JOIN# manifest b ON b.[Route] = a.[Route] AND b.RN = a.RN+1
WHEREa.passob_bit = 0
) c
) d
WHERERN % 2 = 1
I've never quite gotten used to how to use the Modulo % so it's been a challenge to try to get this working as needed.
Any ideas out there?
Much appreciated!
March 22, 2020 at 12:04 am
Not a clue what Modulo has to do with this. Sounds like you want a running total of riders boarding minus a running total of riders leaving the bus. Maybe something like this?
SELECT BusRoute
, StopDateTime
, OnOff
, BusPassengerCount = SUM(OnOff) OVER ( PARTITION BY BusRoute
ORDER BY StopDateTime
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW )
FROM
(SELECT [Route] AS BusRoute
, CustomerID
, StopType
, StopDateTime
, OnOff = CASE WHEN StopType = 'P' THEN 1 ELSE -1 END
FROM #Manifest) stops
ORDER BY stops.BusRoute
, stops.StopDateTime;
March 22, 2020 at 12:16 am
Hi, thanks for your input!
The modulo was only in reference to the code I posted which had "WHERE RN % 2 = 1". It's code from someone else, so I've never quite been sure how to use it.
Unfortunately, this query doesn't quite provide what I'm looking for. I need one record for each total of passengers, plus the RN (of the first passenger) to tie it back to the main dataset of the full route. But only when passengers are actually onboard (as indicated by the passob_bit column = 1).
I posted the results I'm looking for in the #Expected table. Hopefully that's more clear?
March 22, 2020 at 12:33 am
I guess I answered this question, the one you explicitly asked instead of the one you intended:
I'm trying to get a running total of how many riders are on the bus each time someone is onboard the vehicle.
Sounds like you don't want a count of the number of passengers on board between each stop, but more a passenger manifest for each "hop" (between each stop). So I guess you may be able to restate the question/query as:
Show all Passengers that are on the bus "between" each stop. (like the number of passengers on a plane on each leg of a flight (stopping at multiple airports).
Could you please explain what the various columns in your table mean/refer to in the real world? It sounds like your table is describing something like "rides" where passengers ride for several stops anywhere along the "bus" line, and you want the number of passengers on the bus at any given time. If that's the case, then it would be the running count of passengers getting on the bus minus a running count of passengers getting off the bus at any given stop. if you can explain that part, then the rest should be a lot easier.
March 22, 2020 at 1:57 am
Sure, I think you have it right. The TripDateISO is the actual day of service, while the StopDateTime is the date plus the time the passenger (i.e. Customer) is picked or dropped off, the StopType is either (P)ick or (D)rop, the PassengerStatusDescription is just a description of whether someone is boarding or disembarking, the OnOff and PassOB_bit are both just showing when someone is onboard (if > 0).
So passenger A could be picked up, Passenger B could be picked up, Passenger C could be picked up, then Passenger B is dropped off, Passenger D is picked up, Passenger A , then B are dropped off, then Passenger C and then Passenger D are dropped off. That's a total of 4 riders, so 8 records (4 picks and 4 drops). The PassOB column I'm looking for in that scenario would be on Passenger A, with a total of 4, because there were only 4 riders onboard the vehicle until everyone got dropped off. Then it starts over again with another passenger being picked up, Passenger E, etc. All of this is happening on each route, on each day.
I know this is something to do with summing the PassOB_bit column, maybe each time it changes value? So 1, 0, 1, 1, 0,0 would be 1, 2. Since it's the 1 from the first passenger, ignores the zero(drop off), adds together the 1 and 1 for 2 passengers, and ignores the other two zeros for drop offs. (It needs to include the RN on each record total so it can be tied back to the rest of the route data in order, so some records will be null when joined back since we're summing some of the passenger records together.)
Did I explain it any better? I'm not sure if that helped or just made it more confusing. The #Expected value table shows exactly what I'm looking for with the #manifest data table.
March 22, 2020 at 2:13 am
It's the "who's the conductor of the train?" joke, but you really want the number of passengers on the train at any point.
I wouldn't count the bit column, personally. I'd "convert" Enters to +1 and Leaves to -1. Then if you do a running total of Enters minus a running total of Leaves, you have the number of passengers on the bus, right?
March 22, 2020 at 2:18 am
Essentially, yes. If I summed up this new column for each route, it should total the number of passengers that were picked up on that route for the day.
But how would that work, with your answer above? I can't sum the OnOff column (with 1 and -1) since that would just end up zero for each route since it cancels itself out, doesn't it?
March 22, 2020 at 2:25 am
So you just want a total number of passengers picked up during the whole route - doesn't matter when they get on or off? then you'd just count the PassengerIDs for each (date, route) combination, right? As long as each "ride" record has the same RN, you can group by it.
March 22, 2020 at 2:40 am
All this does is select rows where RN is odd
WHERE RN % 2 = 1
I'm not quite sure what you want. Are you looking to get the maximum number of passengers present on the bus at any one time for each route for each day?
What does 'P' and 'D' mean?
March 22, 2020 at 3:02 am
No, perhaps I shouldn't have said anything about totals for routes.
Check out the #Expected results table. That shows exactly what I'm looking for, with totals of how many passengers are onboard whenever there are passengers onboard the vehicle. It is NOT a total of passengers by route. I just said that because the column I'm looking for would just end up being a column that I could theoretically be able to sum/total all the passengers for each route. But I need to see totals each time passengers are onboard. Some passengers will be picked and immediately dropped, which would equal 1, but other times passengers will be picked up, then more, then some dropped off, then everyone in that group dropped off. Each time there's someone onboard I need a total of passengers.
March 22, 2020 at 3:03 am
P = Pick, D = Drop
March 22, 2020 at 3:04 am
StopType is either (P)ick or (D)rop
(That's why I was doing the CASE WHEN StopType = 'D' THEN -1 ELSE 1 END part... to convert those pickups to positive numbers and drops to negative numbers.)
March 22, 2020 at 9:56 pm
I think I had the answer the whole time.....
SELECTTripDateISO
,[Route]
,RN
,LeadRN - RN As [PassOB]
FROM(
SELECTTripDateISO,[Route],RN,
LEAD(RN,1,0) OVER (ORDER BY TripDateISO,[Route],RN) AS [LeadRN],
,OnOff
FROM(
SELECTTripDateISO,[Route],RN,StopDateTIme,OnOff
FROM#Manifest
WHEREPassOB_bit = 0 OR RN = 1
UNION ALL
SELECTt2.TripDateISO,t2.[Route],t2.RN,t2.StopDateTIme,t2.OnOff
FROM#Manifestt1
JOIN#Manifestt2 ON t2.TripDateISO = t1.TripDateISO AND t2.[Route] = t1.[Route] AND t2.RN = t1.RN+1
WHEREt1.PassOB_bit = 0) c
) d
WHEREOnOff = 1
That modulo is what threw it off.
Thanks for taking a look everyone!
March 23, 2020 at 4:18 pm
Please explain your table to me.
Is this fixed route transportation, or on-demand transportation?
If this is a list of stops, does the StopDateTime column represent the SCHEDULED or the ACTUAL date and time? If it's the scheduled, then you probably need to add a column for actual date time. Not necessarily for this query, but I'm guessing that at some point someone will want to track on-time performance metrics.
If it represents the ACTUAL pick up or drop off time, the the query is simple. The count of pickups compared to the count of drop offs will be equal to the number of passengers on-board. If there are 10 pickups, and 2 dropoffs, then there has to be 2 people on the vehicle.
What's the point of the TripDateISO column? It's redundant data with the StopDateTime.
You also have a StopType, a PassengerDecsription, an OnOff , and a pass_OB_Bit column. These are all redundant. It appears that your structure can possibly use some work.
Is this custom software, or is it a third-party? Is it Trapeze?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply