Post reply

Row_Number for grouping to find min and max values

  • December 4, 2019 at 4:43 pm

    #3704388

    Apologies, I changed the time format because it was easier for me to read the times in Time format rather than seconds.  So no underlying changes there, I changed it back to the original seconds in the sample below.  The SpaceOB is generated in this string so I have no control over that one, just like the PassOB field.

    What I don't understand is how to "add the ambulatory to the grouped data and use a Having clause".  I've manually added a column to the table ("NonAmb") which I think would allow me to easily exclude any of the non ambulatory groups, before doing the calculations.  Just don't know how to go about getting that column. See below. (and if you have better ideas, I'm totally open to suggestions)

    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,
    [ActualArriveTime] [int] NULL,
    [ActualDepartTime] [int] 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,
    [NonAmb] [int] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (1, 20190601, 9639930, N'100', N'Pick', 71157, 30080, 30084, N'08:25', N'08', 164943, N'CLI1', 1, N'LR1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (2, 20190601, 9638421, N'100', N'Pick', 75550, 31563, 31856, N'08:30', N'08', 164956, N'CLI2', 1, N'LR1,XW1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (3, 20190601, 9639930, N'100', N'Drop', 71157, 31859, 31860, N'08:51', N'08', 164956, N'CLI1', 1, N'XW1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (4, 20190601, 9638613, N'100', N'Pick', 80861, 32883, 33715, N'08:56', N'08', 164960, N'CLI2', 1, N'XW2', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (5, 20190601, 9638613, N'100', N'Drop', 80861, 34908, 35207, N'09:46', N'09', 164969, N'CLI1', 1, N'XW1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (6, 20190601, 9638421, N'100', N'Drop', 75550, 36558, 36560, N'10:09', N'10', 164974, N'', 0, N'', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (7, 20190601, 9640023, N'100', N'Pick', 82497, 37724, 38031, N'09:52', N'09', 164988, N'CLI1,PCA1', 1, N'AM1,WH1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (8, 20190601, 9640023, N'100', N'Drop', 82497, 39467, 39468, N'10:57', N'10', 164998, N'', 0, N'', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (9, 20190601, 9639753, N'100', N'Pick', 70223, 40135, 40144, N'11:09', N'11', 165000, N'CLI1', 1, N'LR1', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (10, 20190601, 9639753, N'100', N'Drop', 70223, 41007, 41009, N'11:23', N'11', 165007, N'', 0, N'', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (11, 20190601, 9640004, N'100', N'Pick', 82316, 46914, 46974, N'13:06', N'13', 165015, N'CLI1', 1, N'AM1', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (12, 20190601, 9640003, N'100', N'Pick', 82335, 46914, 46974, N'13:06', N'13', 165015, N'CLI2,PCA1', 1, N'AM3', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (13, 20190601, 9640005, N'100', N'Pick', 65384, 46914, 46974, N'13:06', N'13', 165015, N'CLI3,PCA2', 1, N'AM5', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (14, 20190601, 9640006, N'100', N'Pick', 82307, 46914, 46974, N'13:06', N'13', 165015, N'CLI4,PCA3', 1, N'AM7', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (15, 20190601, 9640004, N'100', N'Drop', 82316, 48272, 48274, N'13:24', N'13', 165025, N'CLI3,PCA3', 1, N'AM6', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (16, 20190601, 9640003, N'100', N'Drop', 82335, 48272, 48274, N'13:24', N'13', 165025, N'CLI2,PCA2', 1, N'AM4', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (17, 20190601, 9640005, N'100', N'Drop', 65384, 48272, 48274, N'13:24', N'13', 165025, N'CLI1,PCA1', 1, N'AM2', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (18, 20190601, 9640006, N'100', N'Drop', 82307, 48272, 48274, N'13:24', N'13', 165025, N'', 0, N'', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (19, 20190601, 9639880, N'100', N'Pick', 50795, 53999, 54005, N'15:00', N'15', 165051, N'CLI1,PCA1', 1, N'AM1,WH1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (20, 20190601, 9639880, N'100', N'Drop', 50795, 54935, 54936, N'15:15', N'15', 165055, N'', 0, N'', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (1, 20190601, 9639903, N'101', N'Pick', 81844, 20382, 21381, N'05:48', N'05', 161912, N'CLI1', 1, N'WH1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (2, 20190601, 9639903, N'101', N'Drop', 81844, 22578, 22580, N'06:16', N'06', 161924, N'', 0, N'', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (3, 20190601, 9638211, N'101', N'Pick', 70585, 23887, 23907, N'06:22', N'06', 161941, N'CLI1', 1, N'AM1', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (4, 20190601, 9638211, N'101', N'Drop', 70585, 24585, 24587, N'06:49', N'06', 161944, N'', 0, N'', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (5, 20190601, 9629649, N'101', N'Pick', 79361, 24600, 25200, N'06:50', N'06', 161952, N'CLI1', 1, N'AM1', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (6, 20190601, 9629646, N'101', N'Pick', 77673, 25260, 25860, N'07:00', N'07', 161954, N'CLI2', 1, N'AM2', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (7, 20190601, 9629649, N'101', N'Drop', 79361, 25920, 26520, N'07:22', N'07', 161963, N'CLI1', 1, N'AM1', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (8, 20190601, 9629646, N'101', N'Drop', 77673, 26580, 27180, N'07:33', N'07', 161975, N'', 0, N'', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (9, 20190601, 9639786, N'101', N'Pick', 81668, 27900, 28500, N'08:10', N'08', 161976, N'CLI1', 1, N'LR1', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (10, 20190601, 9639786, N'101', N'Drop', 81668, 28560, 29160, N'08:06', N'08', 161985, N'', 0, N'', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (11, 20190601, 9640058, N'101', N'Pick', 57530, 34200, 35481, N'09:52', N'09', 161989, N'CLI1', 1, N'WH1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (12, 20190601, 9637966, N'101', N'Pick', 62829, 36511, 36880, N'10:16', N'10', 162001, N'CLI2', 1, N'AM1,WH1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (13, 20190601, 9640058, N'101', N'Drop', 57530, 37914, 37916, N'10:31', N'10', 162003, N'CLI1', 1, N'AM1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (14, 20190601, 9637966, N'101', N'Drop', 62829, 38992, 38994, N'10:49', N'10', 162013, N'', 0, N'', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (15, 20190601, 9639766, N'101', N'Pick', 9525, 39191, 39590, N'11:00', N'11', 162014, N'CLI1,PCA1', 1, N'AM1,XW1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (16, 20190601, 9639766, N'101', N'Drop', 9525, 40563, 40565, N'11:16', N'11', 162016, N'', 0, N'', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (17, 20190601, 9629656, N'101', N'Pick', 79726, 42648, 43091, N'11:45', N'11', 162040, N'KID1', 1, N'LR1', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (18, 20190601, 9629656, N'101', N'Drop', 79726, 44030, 44033, N'12:13', N'12', 162045, N'', 0, N'', 0)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (19, 20190601, 9639867, N'101', N'Pick', 63977, 45090, 45097, N'12:15', N'12', 162051, N'CLI1', 1, N'XW1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (20, 20190601, 9629650, N'101', N'Pick', 79361, 46363, 46369, N'12:40', N'12', 162058, N'CLI2', 1, N'AM1,XW1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (21, 20190601, 9639867, N'101', N'Drop', 63977, 47671, 47673, N'13:14', N'13', 162069, N'CLI1', 1, N'AM1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (22, 20190601, 9629650, N'101', N'Drop', 79361, 48271, 48274, N'13:24', N'13', 162071, N'', 0, N'', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (23, 20190601, 9638616, N'101', N'Pick', 80861, 49585, 49888, N'13:30', N'13', 162079, N'CLI1,PCA1', 1, N'AM1,XW1', 1)
    GO
    INSERT [dbo].[TestDataII] ([RN], [LDate], [BookingID], [EvStrName], [Event_Activity], [ClientID], [ActualArriveTime], [ActualDepartTime], [SchTime], [SchHour], [Odometer], [PassOB], [PassOB_bit], [SpaceOB], [NonAmb]) VALUES (24, 20190601, 9638616, N'101', N'Drop', 80861, 51215, 51217, N'14:13', N'14', 162087, N'', 0, N'', 1)
    GO

  • December 5, 2019 at 5:29 pm

    Answer

    #3705053

    Sorry for the delay I thought I had already posted this.

    This is a variation of my solution

    SELECTEvStrName,
    RN,
    LeadRN-RN As [PassOB],
    LeadActualArriveTime-ActualArriveTime AS [Time],
    LeadOdometer-Odometer AS [Miles]
    FROM(
    SELECTEvStrName,RN,
    ActualArriveTime,ActualDepartTime,Odometer,
    LEAD(RN,1,0) OVER (ORDER BY EvStrName,RN) AS [LeadRN],
    LEAD(ActualArriveTime,1,0) OVER (ORDER BY EvStrName,RN) AS [LeadActualArriveTime],
    LEAD(ActualDepartTime,1,0) OVER (ORDER BY EvStrName,RN) AS [LeadActualDepartTime],
    LEAD(Odometer,1,0) OVER (ORDER BY EvStrName,RN) AS [LeadOdometer]
    FROM(
    SELECTEvStrName,RN,ActualArriveTime,ActualDepartTime,Odometer
    FROMdbo.TestDataII
    WHEREPassOB_bit = 0 OR RN = 1
    UNION ALL
    SELECTt2.EvStrName,t2.RN,t2.ActualArriveTime,t2.ActualDepartTime,t2.Odometer
    FROMdbo.TestDataII t1
    JOINdbo.TestDataII t2 ON t2.EvStrName = t1.EvStrName AND t2.RN = t1.RN+1
    WHEREt1.PassOB_bit = 0
    ) c
    ) d
    WHERERN % 2 = 1
    ANDNOT EXISTS (
    SELECT*
    FROMdbo.TestDataII x
    WHEREx.EvStrName = d.EvStrName
    ANDx.RN BETWEEN d.RN AND d.LeadRN
    AND(x.SpaceOB LIKE '%XW%' OR x.SpaceOB LIKE '%WH%') 
    );

     

    • This reply was modified 5 years ago by  David Burrows. Reason: Changed NonAmb in NOT EXISTS

    Far away is close at hand in the images of elsewhere.
    Anon.

  • December 5, 2019 at 5:55 pm

    #3705060

    David, I apologize, I think there was a misunderstanding.  It looks like your query uses the NonAmb column to determine non-ambulatory riders, but the NonAmb column was the one I manually entered to show you what I hoped for with the results.  I don't know how to generate that column via code.    It should show a 1 for each pick and drop in a group if anyone in the group has a SpaceOB of XW or WH, otherwise it would show a 0.

    How would I get this column?  If this column could be generated automatically via code then your solution would be exactly what's needed here (again!).

  • December 5, 2019 at 6:04 pm

    #3705064

    Changed my post above accordingly.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • December 5, 2019 at 6:33 pm

    #3705104

    Wow.  That seems so simple.

    You Rock, David!  This is exactly what I needed, thank you so much!

Viewing 5 posts - 16 through 19 (of 19 total)

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