I have a dataset which I've added row_number to in order to separate out the basic set of data by route number. Each time the route picks up a person it shows they have a person onboard. They could stop multiple times and pick up more people, dropping people at various places along the route as well. I'm trying to identify the odometer (and times) of the vehicle when the first person is picked up and the odometer and times when the last person is dropped off, for a consistent period of time. So I only want the odometers for "clusters" of when we have someone on board.
I have a column showing when (and how many) currently onboard and also when there's no one on board (PassOB), but I also created a bit column with a simple 0 or 1 showing the same thing, which I tried to use to create the clustering I'm initially looking for, but it didn't quite work. The data points I'm looking to grab (so I can do calculations) are the following in parentheses:
person 1 picked up (odometer, arrive, depart)
person 1 dropped off (odometer, arrive, depart)
person 2 picked up (odometer, arrive, depart)
person 3 picked up
person 4 picked up
person 2 dropped off
person 3 dropped off
person 4 dropped off (odometer, arrive, depart)
Below is some test data. (Ignore the odd looking times (actualarrive/actualdepart)columns as they're stored in "seconds after midnight" format which i have to convert to do some calculations between them). BookingID indicates the "person" and the PassOB shows when people are onboard or not. The "cluster" column here is my attempt which didn't work right (as I think rn#6 - 9 should have reflected "3" for all of them to represent the 3rd cluster of riding). Can anyone help point me in the right direction of what I'm doing wrong? Just trying to calculate miles and time for each cluster, but only when we have someone onboard.
USE [TESTDATA]
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (1, N'20190601', N'5701', 12148205, 23100, 23280, 6250015, 283056.99, N'ADA1', 1, 1)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (2, N'20190601', N'5701', 12148205, 24600, 24780, 10937500, 283064.54, NULL, 0, 1)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (3, N'20190601', N'5701', 12148360, 28080, 28260, 27271222, 283084.75, N'ADA1', 1, 2)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (4, N'20190601', N'5701', 12148360, 29820, 30000, 28974667, 283099.16, NULL, 0, 2)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (5, N'20190601', N'5701', 12152168, 30660, 30840, 29673423, 283105.51, N'ADA1', 1, 3)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (6, N'20190601', N'5701', 12148135, 31260, 31440, 30252273, 283107.62, N'ADA2', 1, 4)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (7, N'20190601', N'5701', 12148774, 34020, 34200, 34085005, 283129.78, N'ADA3', 1, 5)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (8, N'20190601', N'5701', 12148774, 34860, 35040, 36501246, 283132.53, N'ADA2', 1, 6)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (9, N'20190601', N'5701', 12152168, 32100, 32280, 40578625, 283115.19, N'ADA1', 1, 7)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (10, N'20190601', N'5701', 12148135, 32640, 32820, 41937772, 283117.45, NULL, 0, 3)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (11, N'20190601', N'5701', 12155595, 35820, 36000, 43296927, 283139.5, N'ADA1', 1, 8)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (12, N'20190601', N'5701', 12155595, 37140, 37320, 43636709, 283147.97, NULL, 0, 4)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (13, N'20190601', N'5701', 12153440, 41280, 41460, 64037389, 283164.98, N'ADA1,PCA1', 1, 9)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (14, N'20190601', N'5701', 12148434, 42660, 42840, 76534044, 283170.6, N'ADA2,PCA1', 1, 10)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (15, N'20190601', N'5701', 12148434, 43620, 43800, 82400516, 283172.32, N'ADA1,PCA1', 1, 11)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (16, N'20190601', N'5701', 12152169, 44760, 44940, 86800375, 283181.18, N'ADA2,PCA1', 1, 12)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (17, N'20190601', N'5701', 12152169, 45840, 46020, 90100288, 283186.55, N'ADA1,PCA1', 1, 13)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (18, N'20190601', N'5701', 12153440, 46440, 46620, 90913608, 283188.81, NULL, 0, 5)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (19, N'20190601', N'5701', 12155942, 48840, 49020, 94343715, 283195.14, N'ADA1', 1, 14)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (20, N'20190601', N'5701', 12155942, 49740, 49920, 95921808, 283198.93, NULL, 0, 6)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (1, N'20190601', N'5702', 12148090, 25020, 25200, 43749963, 4153.34, N'ADA1', 1, 1)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (2, N'20190601', N'5702', 12148088, 26040, 26220, 57812441, 4157.27, N'ADA2', 1, 2)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (3, N'20190601', N'5702', 12148088, 27960, 28140, 68359317, 4162.84, N'ADA1', 1, 3)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (4, N'20190601', N'5702', 12148090, 28860, 29040, 76269459, 4167.65, NULL, 0, 1)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (5, N'20190601', N'5702', 12148377, 33360, 33540, 83314510, 4168.9, N'ADA1', 1, 4)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (6, N'20190601', N'5702', 12153361, 36000, 36180, 83523103, 4187.3, N'ADA2,COM2,PCA1', 1, 5)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (7, N'20190601', N'5702', 12148377, 34560, 34740, 83562228, 4180.63, N'ADA1,COM2,PCA1', 1, 6)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (8, N'20190601', N'5702', 12153361, 36660, 36840, 83679551, 4189.14, NULL, 0, 2)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (9, N'20190601', N'5702', 12148083, 40320, 40500, 92491552, 4191.84, N'ADA1', 1, 7)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (10, N'20190601', N'5702', 12155144, 42660, 42840, 93664770, 4202.93, N'ADA2', 1, 8)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (11, N'20190601', N'5702', 12148083, 41400, 41580, 95776541, 4199.94, N'ADA1', 1, 9)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (12, N'20190601', N'5702', 12148357, 44580, 44760, 95793843, 4213.28, N'ADA2', 1, 10)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (13, N'20190601', N'5702', 12155155, 45600, 45780, 95797062, 4221.73, N'ADA3', 1, 11)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (14, N'20190601', N'5702', 12148357, 46560, 46740, 95806801, 4225.66, N'ADA2', 1, 12)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (15, N'20190601', N'5702', 12155144, 47340, 47520, 96052878, 4229.19, N'ADA1', 1, 13)
GO
INSERT [dbo].[test] ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (16, N'20190601', N'5702', 12155155, 47880, 48060, 96260143, 4231, NULL, 0, 3)
GO
November 22, 2019 at 3:33 am
It would be nice if you provided the CREATE TABLE statment to go along with the test data. Posting the code for your attempt would probably help, as well.,
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2019 at 3:17 pm
Apologies. My mind is a little scattered, apparently.
Fresh data with query:
CREATE TABLE #test
(RN INT
,LDATE INT
,EVSTRNAME VARCHAR(20)
,BOOKINGID INT
,ACTUALARRIVETIME INT
,ACTUALDEPARTTIME INT
,EVORDER INT
,ODOMETER INT
,PASSOB VARCHAR(50)
,PASSOB_BIT INT
,CLUSTER INT
)
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (1, N'20190601', N'5701', 12148205, 23100, 23280, 6250015, 283056.99, N'ADA1', 1, 1)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (2, N'20190601', N'5701', 12148205, 24600, 24780, 10937500, 283064.54, NULL, 0, 1)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (3, N'20190601', N'5701', 12148360, 28080, 28260, 27271222, 283084.75, N'ADA1', 1, 2)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (4, N'20190601', N'5701', 12148360, 29820, 30000, 28974667, 283099.16, NULL, 0, 2)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (5, N'20190601', N'5701', 12152168, 30660, 30840, 29673423, 283105.51, N'ADA1', 1, 3)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (6, N'20190601', N'5701', 12148135, 31260, 31440, 30252273, 283107.62, N'ADA2', 1, 4)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (7, N'20190601', N'5701', 12148774, 34020, 34200, 34085005, 283129.78, N'ADA3', 1, 5)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (8, N'20190601', N'5701', 12148774, 34860, 35040, 36501246, 283132.53, N'ADA2', 1, 6)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (9, N'20190601', N'5701', 12152168, 32100, 32280, 40578625, 283115.19, N'ADA1', 1, 7)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (10, N'20190601', N'5701', 12148135, 32640, 32820, 41937772, 283117.45, NULL, 0, 3)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (11, N'20190601', N'5701', 12155595, 35820, 36000, 43296927, 283139.5, N'ADA1', 1, 8)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (12, N'20190601', N'5701', 12155595, 37140, 37320, 43636709, 283147.97, NULL, 0, 4)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (13, N'20190601', N'5701', 12153440, 41280, 41460, 64037389, 283164.98, N'ADA1,PCA1', 1, 9)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (14, N'20190601', N'5701', 12148434, 42660, 42840, 76534044, 283170.6, N'ADA2,PCA1', 1, 10)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (15, N'20190601', N'5701', 12148434, 43620, 43800, 82400516, 283172.32, N'ADA1,PCA1', 1, 11)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (16, N'20190601', N'5701', 12152169, 44760, 44940, 86800375, 283181.18, N'ADA2,PCA1', 1, 12)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (17, N'20190601', N'5701', 12152169, 45840, 46020, 90100288, 283186.55, N'ADA1,PCA1', 1, 13)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (18, N'20190601', N'5701', 12153440, 46440, 46620, 90913608, 283188.81, NULL, 0, 5)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (19, N'20190601', N'5701', 12155942, 48840, 49020, 94343715, 283195.14, N'ADA1', 1, 14)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (20, N'20190601', N'5701', 12155942, 49740, 49920, 95921808, 283198.93, NULL, 0, 6)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (1, N'20190601', N'5702', 12148090, 25020, 25200, 43749963, 4153.34, N'ADA1', 1, 1)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (2, N'20190601', N'5702', 12148088, 26040, 26220, 57812441, 4157.27, N'ADA2', 1, 2)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (3, N'20190601', N'5702', 12148088, 27960, 28140, 68359317, 4162.84, N'ADA1', 1, 3)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (4, N'20190601', N'5702', 12148090, 28860, 29040, 76269459, 4167.65, NULL, 0, 1)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (5, N'20190601', N'5702', 12148377, 33360, 33540, 83314510, 4168.9, N'ADA1', 1, 4)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (6, N'20190601', N'5702', 12153361, 36000, 36180, 83523103, 4187.3, N'ADA2,COM2,PCA1', 1, 5)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (7, N'20190601', N'5702', 12148377, 34560, 34740, 83562228, 4180.63, N'ADA1,COM2,PCA1', 1, 6)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (8, N'20190601', N'5702', 12153361, 36660, 36840, 83679551, 4189.14, NULL, 0, 2)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (9, N'20190601', N'5702', 12148083, 40320, 40500, 92491552, 4191.84, N'ADA1', 1, 7)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (10, N'20190601', N'5702', 12155144, 42660, 42840, 93664770, 4202.93, N'ADA2', 1, 8)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (11, N'20190601', N'5702', 12148083, 41400, 41580, 95776541, 4199.94, N'ADA1', 1, 9)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (12, N'20190601', N'5702', 12148357, 44580, 44760, 95793843, 4213.28, N'ADA2', 1, 10)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (13, N'20190601', N'5702', 12155155, 45600, 45780, 95797062, 4221.73, N'ADA3', 1, 11)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (14, N'20190601', N'5702', 12148357, 46560, 46740, 95806801, 4225.66, N'ADA2', 1, 12)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (15, N'20190601', N'5702', 12155144, 47340, 47520, 96052878, 4229.19, N'ADA1', 1, 13)
GO
INSERT #test ([rn], [ldate], [evstrname], [bookingid], [actualarrivetime], [actualdeparttime], [evorder], [odometer], [passob], [passob_bit], [cluster]) VALUES (16, N'20190601', N'5702', 12155155, 47880, 48060, 96260143, 4231, NULL, 0, 3)
GO
SELECT *
,ROW_NUMBER() OVER(PARTITION BY LDate,EvStrName,passob_bit ORDER BY LDate,EvStrName,EvOrder) AS cluster
FROM #test
ORDER BY ldate, evstrname, evorder
DROP TABLE #test
The only thing I did was try to create the "cluster" using row_number which didn't work right. My thinking is if I can create this grouping with each time a passenger is onboard, I can grab the odom and times from the time they boarded to the time they dropped off to get the distance and minutes. The thing is, I do need to know when there are multiple riders onboard vs only 1 so I can mark the difference between single rider miles and shared rider miles.
Any thoughts? Am I off in my thinking here for methodology?
November 22, 2019 at 4:22 pm
It would also really help if you gave expected results. I guessed at what you were looking for.
This is a variant on the packing intervals problem. The time that each passenger is on board is an interval of time, and you want to find when there are multiple passengers on board (their intervals overlap).
WITH Passengers AS
(
SELECT *
,ROW_NUMBER() OVER(PARTITION BY LDate, EvStrName, BOOKINGID ORDER BY EVORDER) AS PassengerStatus
FROM #test
)
SELECT *
,CASE WHEN P.PassengerStatus = 1 THEN 'Board' ELSE 'Disembark' END AS PassengerStatusDescription
,SUM(CASE WHEN p.PassengerStatus = 1 THEN 1 ELSE -1 END) OVER(PARTITION BY p.LDATE, p.EVSTRNAME ORDER BY p.EVORDER) AS CurrentPassengerCount
FROM Passengers p
ORDER BY ldate, evstrname, evorder
NOTE: Ordering by partition expressions is pointless. By definition, all records in a partition have the same values for each of the partition expressions. Orderings based on the same value are indistinguishable with respect to that value. You get the same exact results from including the partition expressions in the ORDER BY clause as you do if you do not include the partition expressions. Thus ROW_NUMBER() OVER(PARTITION BY LDate,EvStrName,passob_bit ORDER BY LDate,EvStrName,EvOrder) AS cluster is exactly the same as ROW_NUMBER() OVER(PARTITION BY LDate,EvStrName,passob_bit ORDER BY EvOrder) AS cluster.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 22, 2019 at 4:55 pm
Thank you for your help, but I'm not sure how this helps. I already know when passengers board and disembark in the PassOB and PassOB_Bit columns (plus I have another column for this which isn't included here). What I need to be able to do is be able to grab the odom and times for the min and max of each "grouping" of passengers. If it's one passenger, then get the odom/times when they board, and then when they disembark. If it's multiple passengers I need the first odom/times when the first passenger boarded and the last odom/times of when the last passenger disembarked. I need to do this for each route, every day. I thought by creating the groupings or "clusters" while passengers are on board, I could use this value to get the min/max of grouping by this column.
(Because I can't seem to figure out how to post pictures here) I've attached an excel worksheet where I've added a couple columns to give an idea of the data I'm looking for on this. With the cluster column which I'm hoping to use to get the min/max of each odom in each cluster. The MinMaxOdom column shows which odom values I need in each cluster so I can do the math shown in the Mile Calc column. (eventually I'll have to also do this with the actualarrivetime and actualdeparttimes too, but that's more complexity here with getting the min arrivetime of each cluster and max departtime of each cluster).
Hoping this makes sense.
November 22, 2019 at 6:01 pm
People are hesitant to open Excel files posted on the Internet. You should post the expected results the same way that you posted the sample data: create a temp table and then insert the expected results into the temp table.
As I said, this is a packing intervals problem. The complete solution requires several steps, but it wasn't clear whether you need the complete solution or one of the intermediate steps. This is why I suggested that you post the expected results. It also wasn't clear which of your fields were basic data and which were calculated previously.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 22, 2019 at 7:12 pm
I'm all for seeing how someone would do this start to end, I just wasn't sure if I was asking too much. And I wasn't sure if my original inquiry on the min/max for clusters/groups was the right way to approach the final product or not.
Below is the data rescripted with additional columns showing what I hope to get with the results. With these columns I can aggregate miles which are shared and miles which are not shared, by route, by day. (I did not include the times here in the expected results since I figure if I can get the odom/miles figured out I can do the times myself)
CREATE TABLE #ExpectedResults(
[rn] [int] NULL,
[ldate] [int] NULL,
[BookingID] [int] NULL,
[Evstrname] [varchar](20) NULL,
[ActualArriveTime] [int] NULL,
[ActualDepartTime] [int] NULL,
[EvOrder] [int] NULL,
[Odometer] [int] NULL,
[PassOB] [nvarchar](255) NULL,
[passOB_bit] [int] NULL,
[Cluster] [int] NULL,
[Shared] [int] NULL,
[MinMaxOdom] [int] NULL,
[MileCalc] [int] NULL
) ON [PRIMARY]
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (1, 20190601, 12148205, 5701, 23100, 23280, 6250015, 283056.99, N'ADA1', 1, 1, 0, 283056.99, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (2, 20190601, 12148205, 5701, 24600, 24780, 10937500, 283064.54, N'NULL', NULL, 1, 0, 283064.54, 8)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (3, 20190601, 12148360, 5701, 28080, 28260, 27271222, 283084.75, N'ADA1', 1, 2, 0, 283084.75, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (4, 20190601, 12148360, 5701, 29820, 30000, 28974667, 283099.16, N'NULL', NULL, 2, 0, 283099.16, 14)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (5, 20190601, 12152168, 5701, 30660, 30840, 29673423, 283105.51, N'ADA1', 1, 3, 1, 283105.51, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (6, 20190601, 12148135, 5701, 31260, 31440, 30252273, 283107.62, N'ADA2', 1, 3, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (7, 20190601, 12148774, 5701, 34020, 34200, 34085005, 283129.78, N'ADA3', 1, 3, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (8, 20190601, 12148774, 5701, 34860, 35040, 36501246, 283132.53, N'ADA2', 1, 3, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (9, 20190601, 12152168, 5701, 32100, 32280, 40578625, 283115.19, N'ADA1', 1, 3, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (10, 20190601, 12148135, 5701, 32640, 32820, 41937772, 283117.45, N'NULL', NULL, 3, 1, 283117.45, 11)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (11, 20190601, 12155595, 5701, 35820, 36000, 43296927, 283139.5, N'ADA1', 1, 4, 0, 283139.5, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (12, 20190601, 12155595, 5701, 37140, 37320, 43636709, 283147.97, N'NULL', NULL, 4, 0, 283147.97, 8)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (13, 20190601, 12153440, 5701, 41280, 41460, 64037389, 283164.98, N'ADA1,PCA1', 1, 5, 1, 283164.98, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (14, 20190601, 12148434, 5701, 42660, 42840, 76534044, 283170.6, N'ADA2,PCA1', 1, 5, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (15, 20190601, 12148434, 5701, 43620, 43800, 82400516, 283172.32, N'ADA1,PCA1', 1, 5, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (16, 20190601, 12152169, 5701, 44760, 44940, 86800375, 283181.18, N'ADA2,PCA1', 1, 5, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (17, 20190601, 12152169, 5701, 45840, 46020, 90100288, 283186.55, N'ADA1,PCA1', 1, 5, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (18, 20190601, 12153440, 5701, 46440, 46620, 90913608, 283188.81, N'NULL', NULL, 5, 1, 283188.81, 24)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (19, 20190601, 12155942, 5701, 48840, 49020, 94343715, 283195.14, N'ADA1', 1, 6, 0, 283195.14, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (20, 20190601, 12155942, 5701, 49740, 49920, 95921808, 283198.93, N'NULL', NULL, 6, 0, 283198.93, 4)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (1, 20190601, 12148090, 5702, 25020, 25200, 43749963, 4153.34, N'ADA1', 1, 1, 1, 4153.34, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (2, 20190601, 12148088, 5702, 26040, 26220, 57812441, 4157.27, N'ADA2', 1, 1, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (3, 20190601, 12148088, 5702, 27960, 28140, 68359317, 4162.84, N'ADA1', 1, 1, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (4, 20190601, 12148090, 5702, 28860, 29040, 76269459, 4167.65, N'NULL', NULL, 1, 1, 4167.65, 15)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (5, 20190601, 12148377, 5702, 33360, 33540, 83314510, 4168.9, N'ADA1', 1, 2, 1, 4168.9, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (6, 20190601, 12153361, 5702, 36000, 36180, 83523103, 4187.3, N'ADA2,COM2,PCA1', 1, 2, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (7, 20190601, 12148377, 5702, 34560, 34740, 83562228, 4180.63, N'ADA1,COM2,PCA1', 1, 2, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (8, 20190601, 12153361, 5702, 36660, 36840, 83679551, 4189.14, N'NULL', NULL, 2, 1, 4189.14, 20)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (9, 20190601, 12148083, 5702, 40320, 40500, 92491552, 4191.84, N'ADA1', 1, 3, 1, 4191.84, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (10, 20190601, 12155144, 5702, 42660, 42840, 93664770, 4202.93, N'ADA2', 1, 3, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (11, 20190601, 12148083, 5702, 41400, 41580, 95776541, 4199.94, N'ADA1', 1, 3, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (12, 20190601, 12148357, 5702, 44580, 44760, 95793843, 4213.28, N'ADA2', 1, 3, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (13, 20190601, 12155155, 5702, 45600, 45780, 95797062, 4221.73, N'ADA3', 1, 3, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (14, 20190601, 12148357, 5702, 46560, 46740, 95806801, 4225.66, N'ADA2', 1, 3, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (15, 20190601, 12155144, 5702, 47340, 47520, 96052878, 4229.19, N'ADA1', 1, 3, 1, NULL, NULL)
GO
INSERT #ExpectedResults ([rn], [ldate], [BookingID], [Evstrname], [ActualArriveTime], [ActualDepartTime], [EvOrder], [Odometer], [PassOB], [passOB_bit], [Cluster], [Shared], [MinMaxOdom], [MileCalc]) VALUES (16, 20190601, 12155155, 5702, 47880, 48060, 96260143, 4231, N'NULL', NULL, 3, 1, 4231, 39)
GO
Again, I appreciate your insight on this. This is just a little further outside of my expertise so I'm running up against a wall.
Thanks
November 26, 2019 at 5:17 pm
Withdrawn. I didn't look at the expected result set carefully.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 26, 2019 at 6:35 pm
Thanks Dixie, but this just groups each passenger into a group. Not quite what I was looking for. If you look at the last set of test data with the "#ExpectedResults" table, it shows the Cluster column of grouping while a passenger is onboard. So if one passenger is picked up and then another and another, (so while someone is onboard ), that whole group would be one group. Not each time we pick up a passenger.
Does that make sense? Hard to explain I guess.
November 26, 2019 at 7:05 pm
Yes, I missed the concept. I was simply keying off the PASSOB column. I'll go back and re-read more carefully. Apologies.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 2, 2019 at 5:41 pm
Not elegant but...
SELECTEVSTRNAME,RN,PASSOB,[TIME],MILES
FROM(
SELECTEVSTRNAME,RN,
LEAD(RN,1,0) OVER (ORDER BY EVSTRNAME,RN)-RN AS [PASSOB],
LEAD(ACTUALARRIVETIME,1,0) OVER (ORDER BY EVSTRNAME,RN)-ACTUALDEPARTTIME AS [TIME],
LEAD(ODOMETER,1,0) OVER (ORDER BY EVSTRNAME,RN)-ODOMETER AS [MILES]
FROM(
SELECTEVSTRNAME,RN,ACTUALARRIVETIME,ACTUALDEPARTTIME,ODOMETER
FROM#test
WHEREpassob_bit = 0 OR RN = 1
UNION ALL
SELECTb.EVSTRNAME,b.RN,b.ACTUALARRIVETIME,b.ACTUALDEPARTTIME,b.ODOMETER
FROM#test a
JOIN#test b ON b.EVSTRNAME = a.EVSTRNAME AND b.RN = a.RN+1
WHEREa.passob_bit = 0
) c
) d
WHERERN % 2 = 1
Far away is close at hand in the images of elsewhere.
Anon.
December 2, 2019 at 6:14 pm
David, elegant or not, I'm impressed!
I'm going through trying to deconstruct it so I understand how it works, but in validating the data this looks spot on for what I needed. Thank you so much, I can't tell you how helpful this is going to be.
One question, what if I have to run this query against an older SQL version that doesn't support the LEAD window function? I know I'm going to run into this issue at some point. I'm guessing the query would turn into a CTE with multiple queries to determine the Lead "manually" using the RN column?
December 3, 2019 at 8:43 am
... older SQL version … I'm guessing the query would turn into a CTE with multiple queries to determine the Lead "manually" using the RN column?
Correct (or a temp table if performance is an issue). I can think of several ways to do it, it is a matter of experimenting.
Far away is close at hand in the images of elsewhere.
Anon.
December 4, 2019 at 1:50 am
Ok, now of course, someone makes this even more complex.
I offered the results of this wonderful piece of coding and the first thing they say is "great, but now we need to see the same information but only for those passenger groups which are ambulatory".
This adds another layer of complexity to this, with another column of information "SpaceOB". If the SpaceOB for anyone (either riding alone or in a group) is like %XW% or %WH% , that means they're non ambulatory and we want to somehow note that so we can exclude them from the calculations (listed in the previous answer from David!). However, I can't just automatically exclude them from the initial data pull altogether because I need to determine if there is any mixture of ambulatory and non ambulatory passengers riding together at the same time. And if there is a mix, I need to be able to identify (and then throw out) ALL those trips in that cluster/group, not just the non-ambulatory ones.
I was wondering if the original "cluster" column that was noted in the last dataset provided would work for this purpose. If that column could be determined for each group, making each clustering of passengers a unique number (and it could include unique numbers for single passengers too, just a unique column to be able to group by for an entire cluster) then I could look for any spaceon matching what I'm looking for, grouped by that cluster column. Or am I missing a key element to this? Or, there's probably an even better way to tackle this I'm sure, I just don't know it.
Final result would be the ability to identify those groups/trips that are "non ambulatory" so they can be excluded prior to doing the [Shared] Miles and [Shared] Time calculations that David provided. The original version does this for all riders, but now this version needs to do it only for groups/trips which are not non ambulatory, based on that SpaceOn field.
Thoughts?
Below is the new set of data with this new column:
CREATE TABLE [dbo].[TestDataII](
[RN] [bigint] NULL,
[LDate] [int] NULL,
[BookingID] [int] NULL,
[EvStrName] [varchar](20) NULL,
[Event_Activity] [varchar](4) NULL,
[ClientID] [int] NULL,
[AAT] [char](5) NULL,
[ADT] [char](5) NULL,
[SchTime] [varchar](5) NULL,
[SchHour] [varchar](2) NULL,
[Odometer] [int] NULL,
[PassOB] [varchar](50) NULL,
[PassOB_bit] [int] NOT NULL,
[SpaceOB] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (1, 20190601, 9639930, N'100', N'Pick', 71157, N'08:21', N'08:21', N'08:25', N'08', 164943, N'CLI1', 1, N'LR1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (2, 20190601, 9638421, N'100', N'Pick', 75550, N'08:46', N'08:50', N'08:30', N'08', 164956, N'CLI2', 1, N'LR1,XW1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (3, 20190601, 9639930, N'100', N'Drop', 71157, N'08:50', N'08:51', N'08:51', N'08', 164956, N'CLI1', 1, N'XW1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (4, 20190601, 9638613, N'100', N'Pick', 80861, N'09:08', N'09:21', N'08:56', N'08', 164960, N'CLI2', 1, N'XW2')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (5, 20190601, 9638613, N'100', N'Drop', 80861, N'09:41', N'09:46', N'09:46', N'09', 164969, N'CLI1', 1, N'XW1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (6, 20190601, 9638421, N'100', N'Drop', 75550, N'10:09', N'10:09', N'10:09', N'10', 164974, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (7, 20190601, 9640023, N'100', N'Pick', 82497, N'10:28', N'10:33', N'09:52', N'09', 164988, N'CLI1,PCA1', 1, N'AM1,WH1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (8, 20190601, 9640023, N'100', N'Drop', 82497, N'10:57', N'10:57', N'10:57', N'10', 164998, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (9, 20190601, 9639753, N'100', N'Pick', 70223, N'11:08', N'11:09', N'11:09', N'11', 165000, N'CLI1', 1, N'LR1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (10, 20190601, 9639753, N'100', N'Drop', 70223, N'11:23', N'11:23', N'11:23', N'11', 165007, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (11, 20190601, 9640004, N'100', N'Pick', 82316, N'13:01', N'13:02', N'13:06', N'13', 165015, N'CLI1', 1, N'AM1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (12, 20190601, 9640003, N'100', N'Pick', 82335, N'13:01', N'13:02', N'13:06', N'13', 165015, N'CLI2,PCA1', 1, N'AM3')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (13, 20190601, 9640005, N'100', N'Pick', 65384, N'13:01', N'13:02', N'13:06', N'13', 165015, N'CLI3,PCA2', 1, N'AM5')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (14, 20190601, 9640006, N'100', N'Pick', 82307, N'13:01', N'13:02', N'13:06', N'13', 165015, N'CLI4,PCA3', 1, N'AM7')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (15, 20190601, 9640004, N'100', N'Drop', 82316, N'13:24', N'13:24', N'13:24', N'13', 165025, N'CLI3,PCA3', 1, N'AM6')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (16, 20190601, 9640003, N'100', N'Drop', 82335, N'13:24', N'13:24', N'13:24', N'13', 165025, N'CLI2,PCA2', 1, N'AM4')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (17, 20190601, 9640005, N'100', N'Drop', 65384, N'13:24', N'13:24', N'13:24', N'13', 165025, N'CLI1,PCA1', 1, N'AM2')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (18, 20190601, 9640006, N'100', N'Drop', 82307, N'13:24', N'13:24', N'13:24', N'13', 165025, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (19, 20190601, 9639880, N'100', N'Pick', 50795, N'14:59', N'15:00', N'15:00', N'15', 165051, N'CLI1,PCA1', 1, N'AM1,WH1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (20, 20190601, 9639880, N'100', N'Drop', 50795, N'15:15', N'15:15', N'15:15', N'15', 165055, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (1, 20190601, 9639903, N'101', N'Pick', 81844, N'05:39', N'05:56', N'05:48', N'05', 161912, N'CLI1', 1, N'WH1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (2, 20190601, 9639903, N'101', N'Drop', 81844, N'06:16', N'06:16', N'06:16', N'06', 161924, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (3, 20190601, 9638211, N'101', N'Pick', 70585, N'06:38', N'06:38', N'06:22', N'06', 161941, N'CLI1', 1, N'AM1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (4, 20190601, 9638211, N'101', N'Drop', 70585, N'06:49', N'06:49', N'06:49', N'06', 161944, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (5, 20190601, 9629649, N'101', N'Pick', 79361, N'06:50', N'07:00', N'06:50', N'06', 161952, N'CLI1', 1, N'AM1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (6, 20190601, 9629646, N'101', N'Pick', 77673, N'07:01', N'07:11', N'07:00', N'07', 161954, N'CLI2', 1, N'AM2')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (7, 20190601, 9629649, N'101', N'Drop', 79361, N'07:12', N'07:22', N'07:22', N'07', 161963, N'CLI1', 1, N'AM1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (8, 20190601, 9629646, N'101', N'Drop', 77673, N'07:23', N'07:33', N'07:33', N'07', 161975, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (9, 20190601, 9639786, N'101', N'Pick', 81668, N'07:45', N'07:55', N'08:10', N'08', 161976, N'CLI1', 1, N'LR1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (10, 20190601, 9639786, N'101', N'Drop', 81668, N'07:56', N'08:06', N'08:06', N'08', 161985, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (11, 20190601, 9640058, N'101', N'Pick', 57530, N'09:30', N'09:51', N'09:52', N'09', 161989, N'CLI1', 1, N'WH1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (12, 20190601, 9637966, N'101', N'Pick', 62829, N'10:08', N'10:14', N'10:16', N'10', 162001, N'CLI2', 1, N'AM1,WH1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (13, 20190601, 9640058, N'101', N'Drop', 57530, N'10:31', N'10:31', N'10:31', N'10', 162003, N'CLI1', 1, N'AM1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (14, 20190601, 9637966, N'101', N'Drop', 62829, N'10:49', N'10:49', N'10:49', N'10', 162013, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (15, 20190601, 9639766, N'101', N'Pick', 9525, N'10:53', N'10:59', N'11:00', N'11', 162014, N'CLI1,PCA1', 1, N'AM1,XW1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (16, 20190601, 9639766, N'101', N'Drop', 9525, N'11:16', N'11:16', N'11:16', N'11', 162016, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (17, 20190601, 9629656, N'101', N'Pick', 79726, N'11:50', N'11:58', N'11:45', N'11', 162040, N'KID1', 1, N'LR1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (18, 20190601, 9629656, N'101', N'Drop', 79726, N'12:13', N'12:13', N'12:13', N'12', 162045, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (19, 20190601, 9639867, N'101', N'Pick', 63977, N'12:31', N'12:31', N'12:15', N'12', 162051, N'CLI1', 1, N'XW1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (20, 20190601, 9629650, N'101', N'Pick', 79361, N'12:52', N'12:52', N'12:40', N'12', 162058, N'CLI2', 1, N'AM1,XW1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (21, 20190601, 9639867, N'101', N'Drop', 63977, N'13:14', N'13:14', N'13:14', N'13', 162069, N'CLI1', 1, N'AM1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (22, 20190601, 9629650, N'101', N'Drop', 79361, N'13:24', N'13:24', N'13:24', N'13', 162071, N'', 0, N'')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (23, 20190601, 9638616, N'101', N'Pick', 80861, N'13:46', N'13:51', N'13:30', N'13', 162079, N'CLI1,PCA1', 1, N'AM1,XW1')
GO
INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [AAT], [ADT], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB]) VALUES (24, 20190601, 9638616, N'101', N'Drop', 80861, N'14:13', N'14:13', N'14:13', N'14', 162087, N'', 0, N'')
GO
December 4, 2019 at 9:06 am
OK several things. The data is different which requires rewriting the query. How is this data generated? Is it going to continually change format as this will make any solutions we provide difficult to sustain. The time difference will now need to be calculated (will times span across day(s)). I think the basic answer will be to add ambulatory to the grouped data and use a HAVING clause to filter the result.
I do have a concern regarding SpaceOB and using LIKE %XW% or %WH% as this could be performance killer, hence my question as to how the data generated and the requirement to aggregate ambulatory for the HAVING clause.
And, p.s. we will need to see the expected results using the test data you provided
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply