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 =...

P = Pick, D = Drop

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...

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...

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...

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...

For a subscription that you just want to run during a set period of time each day, I just created the subscription to run hourly, then modified the SQL Agent...

I have spent several hours/days trying to figure this out with no luck, which is why I posted the question here.  I may not be looking at it the right...

I'll add that the final expected output doesn't necessarily have to include all those columns.  The minimum would be date, EvstrName, RN, and PassCount (for each grouping).  The final result...

• This reply was modified 5 years, 7 months ago by tacy.highland.
• This reply was modified 5 years, 7 months ago by tacy.highland.

Apologies on the permanent table.  I dumped the data I had into a permanent table so I could use Generate Scripts to script out the table schema and data at...

Drew, not quite.  That previous thread provided a count of the number of events where a passenger is onboard with another passenger.  So for example (this is actually in the...

Wow.  That seems so simple.

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

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...

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...

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...