Not wanting to use a Cursor, but...

Question

Post reply

Not wanting to use a Cursor, but...

jwilkerson@valleymetro.org

SSC Enthusiast

Points: 187
More actions
June 2, 2023 at 12:43 am
Go to Answer
#4202649
Ok, need someone to talk me off the cursor ledge on this one.
I have some train stop data that I need to accumulate the number of riders for, over a time when the train is heading in one direction, on a specific day.
The data is all stored on a yearly basis, so I thought I could generate a table of the days that each car was in operation, using something like this:
```
SELECT DISTINCT ArriveDate, Car
FROM [Ridership].[dbo].[DilaxRailGIS2019] 
ORDER BY ArriveDate, car
```
And then I could use a cursor to cycle through each of those records, and select the daily subset of stops per each car, using something like this as a cursor:
```
SELECT arrival, direction, totalIn, totalOut, StationId, StationName
FROM [Ridership].[dbo].[Rail2019] 
WHERE [ArriveDate] = @ArriveDate AND Car = @Car
ORDER BY arrival
```
Which would provide this:
But then I need to accumulate the number of riders per station based on the totalIn/totalOut columns, and for each direction (there are multiple E/W segments for each day).
I started writing a cursor for the first data selected, and then select for the ridership per day and car, but then I am left with needing another cursor to make sure that I am only getting the accumulated totals per direction, and totaled by station. I'm thinking this might be better done through a Python cursor(s) or something, but if anyone has any suggestions i'd love to hear them.
Thank you.

drew.allen

SSC Guru

Points: 76985

June 5, 2023 at 3:16 pm

Answer

#4204831

I think the following will handle resetting the value to zero when the date changes and the direction changes. I've added a calculation for the arrival date, since I'm using it for the partitions.

WITH Reversals AS
(
    SELECT s.ID
 , s.arrival
 , s.direction
 , s.totalIn
 , s.totalOut
 , s.Car
 , s.StationId
 , s.StationName
         , CAST(s.arrival AS DATE) AS arrival_dt
         , CASE WHEN s.direction = LAG(s.direction) OVER(PARTITION BY CAST(s.arrival AS DATE), s.Car ORDER BY s.arrival)
                THEN 0
                ELSE 1
           END AS is_reversal
    FROM #testStationSum AS s
)
, Trips AS
(
    SELECT r.ID
     , r.arrival
         , r.arrival_dt
     , r.direction
     , r.totalIn
     , r.totalOut
     , r.Car
     , r.StationId
     , r.StationName
     , SUM(r.is_reversal) OVER(PARTITION BY r.arrival_dt, r.Car ORDER BY r.arrival ROWS UNBOUNDED PRECEDING) AS Trip_Grp
    FROM Reversals AS r
)
SELECT t.Car
     , t.direction
     , t.StationName
     , t.arrival
     , t.totalIn
     , t.totalOut
     , SUM(t.totalIn - t.totalOut) OVER(PARTITION BY t.arrival_dt, t.Car, t.Trip_Grp ORDER BY t.arrival) AS StationRiders
FROM Trips AS t

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

SELECT y.ArriveDate, y.Car, x.arrival, x.direction, x.totalIn, x.totalOut, x.StationId, x.StationName
  FROM [Ridership].[dbo].[Rail2019] x
 INNER JOIN (SELECT DISTINCT ArriveDate, Car
               FROM [Ridership].[dbo].[DilaxRailGIS2019]) y
         ON y.ArriveDate = x.ArriveDate
        AND y.Car = x.Car
ORDER BY y.ArriveDate, y.car, x.arrival
;

or

;WITH CTE AS
(
    SELECT DISTINCT ArriveDate, Car
      FROM [Ridership].[dbo].[DilaxRailGIS2019] 
)
SELECT CTE.ArriveDate, CTE.Car, x.arrival, x.direction, x.totalIn, x.totalOut, x.StationId, x.StationName
  FROM [Ridership].[dbo].[Rail2019] x
 INNER JOIN CTE 
         ON CTE.ArriveDate = x.ArriveDate
        AND CTE.Car = x.Car
ORDER BY CTE.ArriveDate, CTE.car, x.arrival
;

If you need to sum a column you can just do this with:

SUM(column_name) OVER (PARTITION BY ArriveDate, car, direction ORDER BY arrival)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

Since you're new, please see the article at the first link in my signature line below if you want tested code. For me, it's super rare for me to post anything I haven't actually tested and I just don't have the time to copy from a graphic.

And welcome aboard!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jwilkerson@valleymetro.org SSC Enthusiast Points: 187 More actions · Answer 3

Thanks for the suggestions. Sorry about not making it easier to work with the data, here is the data setup info:

-- Remove the table if it already exists
IF OBJECT_ID('TempDB..#testStationSum', 'U') IS NOT NULL
  DROP TABLE #testStationSum

-- Create the Test Table
CREATE TABLE [dbo].[#testStationSum](
[ID] [int] IDENTITY(1,1) PRIMARY KEY Clustered,
[arrival] [datetime2](7) NULL,
[direction] [nvarchar](6) NULL,
[totalIn] [int] NULL,
[totalOut] [int] NULL,
[Car][nvarchar](10) NULL,
[StationId] [int] NULL,
[StationName] [nvarchar](255) NULL
) ON [PRIMARY]

-- Special required conditions
SET DATEFORMAT MDY

-- All Inserts into the IDENTITY colun
SET Identity_Insert #testSTationSum ON

-- Insert the test data
SELECT '2019-01-01 03:53:47.0000000','W','2','0','145','21','44th St / Washington' UNION ALL
SELECT '2019-01-01 03:56:03.0000000','W','5','3','145','20','38th St / Washington' UNION ALL
SELECT '2019-01-01 03:59:53.0000000','W','1','0','145','19','24th St / Washington' UNION ALL
SELECT '2019-01-01 04:04:09.0000000','W','2','0','145','18','12th St / Washington' UNION ALL
SELECT '2019-01-01 04:07:11.0000000','W','4','1','145','17','3rd St / Washington' UNION ALL
SELECT '2019-01-01 04:09:58.0000000','W','1','7','145','16','Washington / Central Ave' UNION ALL
SELECT '2019-01-01 04:11:47.0000000','W','4','3','145','15','Van Buren / Central Ave' UNION ALL
SELECT '2019-01-01 04:13:51.0000000','W','4','3','145','14','Roosevelt / Central Ave' UNION ALL
SELECT '2019-01-01 04:15:27.0000000','W','1','1','145','13','McDowell / Central Ave' UNION ALL
SELECT '2019-01-01 04:17:20.0000000','W','2','1','145','12','Encanto / Central Ave' UNION ALL
SELECT '2019-01-01 04:19:05.0000000','W','2','4','145','11','Thomas / Central Ave' UNION ALL
SELECT '2019-01-01 04:20:51.0000000','W','3','2','145','10','Osborn / Central Ave' UNION ALL
SELECT '2019-01-01 04:22:57.0000000','W','3','3','145','9','Indian School / Central Ave' UNION ALL
SELECT '2019-01-01 04:24:29.0000000','W','3','1','145','8','Campbell / Central Ave' UNION ALL
SELECT '2019-01-01 04:26:40.0000000','W','2','2','145','7','Central Ave / Camelback' UNION ALL
SELECT '2019-01-01 04:28:28.0000000','W','2','2','145','6','7th Ave / Camelback' UNION ALL
SELECT '2019-01-01 04:31:00.0000000','W','2','4','145','5','19th Ave / Camelback' UNION ALL
SELECT '2019-01-01 04:34:28.0000000','W','2','4','145','4','Montebello / 19th Ave' UNION ALL
SELECT '2019-01-01 04:37:39.0000000','W','2','3','145','3','Glendale / 19th Ave' UNION ALL
SELECT '2019-01-01 04:40:45.0000000','W','2','1','145','2','Northern / 19th Ave' UNION ALL
SELECT '2019-01-01 04:44:30.0000000','E','14','13','145','1','19th Ave / Dunlap' UNION ALL
SELECT '2019-01-01 04:53:58.0000000','E','1','0','145','2','Northern / 19th Ave' UNION ALL
SELECT '2019-01-01 04:56:50.0000000','E','3','0','145','3','Glendale / 19th Ave' UNION ALL
SELECT '2019-01-01 05:00:14.0000000','E','4','4','145','4','Montebello / 19th Ave' UNION ALL
SELECT '2019-01-01 05:07:38.0000000','E','1','2','145','5','19th Ave / Camelback' UNION ALL
SELECT '2019-01-01 05:10:08.0000000','E','0','0','145','6','7th Ave / Camelback' UNION ALL
SELECT '2019-01-01 05:11:53.0000000','E','1','0','145','7','Central Ave / Camelback' UNION ALL
SELECT '2019-01-01 05:13:57.0000000','E','0','0','145','8','Campbell / Central Ave' UNION ALL
SELECT '2019-01-01 05:15:25.0000000','E','2','0','145','9','Indian School / Central Ave' UNION ALL
SELECT '2019-01-01 05:17:27.0000000','E','0','1','145','10','Osborn / Central Ave' UNION ALL
SELECT '2019-01-01 05:19:04.0000000','E','3','2','145','11','Thomas / Central Ave' UNION ALL
SELECT '2019-01-01 05:21:09.0000000','E','0','0','145','12','Encanto / Central Ave' UNION ALL
SELECT '2019-01-01 05:23:08.0000000','E','4','2','145','13','McDowell / Central Ave' UNION ALL
SELECT '2019-01-01 05:24:40.0000000','E','1','1','145','14','Roosevelt / Central Ave' UNION ALL
SELECT '2019-01-01 05:26:43.0000000','E','2','0','145','15','Van Buren / 1st Ave' UNION ALL
SELECT '2019-01-01 05:28:58.0000000','E','0','1','145','16','Jefferson / 1st Ave' UNION ALL
SELECT '2019-01-01 05:30:58.0000000','E','1','1','145','17','3rd St / Jefferson' UNION ALL
SELECT '2019-01-01 05:33:42.0000000','E','0','1','145','18','12th St / Jefferson' UNION ALL
SELECT '2019-01-01 05:37:48.0000000','E','2','1','145','19','24th St / Jefferson' UNION ALL
SELECT '2019-01-01 05:41:53.0000000','E','1','0','145','20','38th St / Washington' UNION ALL
SELECT '2019-01-01 05:44:00.0000000','E','4','14','145','21','44th St / Washington' UNION ALL
SELECT '2019-01-01 05:48:33.0000000','E','1','3','145','23','Priest Dr / Washington St' UNION ALL
SELECT '2019-01-01 05:50:34.0000000','E','0','1','145','24','Center Pkwy / Washington' UNION ALL
SELECT '2019-01-01 05:53:47.0000000','E','1','3','145','25','Mill Ave / 3rd St' UNION ALL
SELECT '2019-01-01 05:55:27.0000000','E','2','3','145','26','Veterans Way / College Ave' UNION ALL
SELECT '2019-01-01 05:58:22.0000000','E','0','1','145','28','University Dr / Rural Rd' UNION ALL
SELECT '2019-01-01 06:01:10.0000000','E','0','0','145','27','Dorsey Ln / Apache Blvd' UNION ALL
SELECT '2019-01-01 06:03:15.0000000','E','0','2','145','29','McClintock Dr / Apache Blvd' UNION ALL
SELECT '2019-01-01 06:05:07.0000000','E','0','0','145','30','Smith-Martin / Apache Blvd' UNION ALL
SELECT '2019-01-01 06:07:25.0000000','E','0','0','145','31','Price-101 / Apache Blvd' UNION ALL
SELECT '2019-01-01 06:10:32.0000000','E','0','0','145','32','Sycamore / Main St' UNION ALL
SELECT '2019-01-01 06:13:37.0000000','E','0','1','145','33','Alma School / Main St' UNION ALL
SELECT '2019-01-01 06:16:14.0000000','E','0','0','145','34','Country Club / Main St' UNION ALL
SELECT '2019-01-01 06:18:12.0000000','E','0','0','145','35','Center / Main St' UNION ALL
SELECT '2019-01-01 06:20:08.0000000','W','5','1','145','36','Mesa Dr / Main St' UNION ALL
SELECT '2019-01-01 06:31:16.0000000','W','5','3','145','35','Center / Main St' UNION ALL
SELECT '2019-01-01 06:33:39.0000000','W','2','1','145','34','Country Club / Main St' UNION ALL
SELECT '2019-01-01 06:37:26.0000000','W','2','0','145','33','Alma School / Main St' UNION ALL
SELECT '2019-01-01 06:39:50.0000000','W','4','7','145','32','Sycamore / Main St' UNION ALL
SELECT '2019-01-01 06:42:28.0000000','W','6','1','145','31','Price-101 / Apache Blvd' UNION ALL
SELECT '2019-01-01 06:44:44.0000000','W','4','2','145','30','Smith-Martin / Apache Blvd' UNION ALL
SELECT '2019-01-01 06:46:16.0000000','W','3','3','145','29','McClintock Dr / Apache Blvd' UNION ALL
SELECT '2019-01-01 06:48:27.0000000','W','0','2','145','27','Dorsey Ln / Apache Blvd' UNION ALL
SELECT '2019-01-01 06:54:40.0000000','W','3','0','145','28','University Dr / Rural Rd' UNION ALL
SELECT '2019-01-01 06:57:36.0000000','W','0','4','145','26','Veterans Way / College Ave' UNION ALL
SELECT '2019-01-01 06:59:19.0000000','W','5','3','145','25','Mill Ave / 3rd St' UNION ALL
SELECT '2019-01-01 07:02:08.0000000','W','0','1','145','24','Center Pkwy / Washington' UNION ALL
SELECT '2019-01-01 07:03:58.0000000','W','3','1','145','23','Priest Dr / Washington St' UNION ALL
SELECT '2019-01-01 07:08:02.0000000','W','5','5','145','21','44th St / Washington' UNION ALL
SELECT '2019-01-01 07:10:06.0000000','W','3','2','145','20','38th St / Washington' UNION ALL
SELECT '2019-01-01 07:13:44.0000000','W','1','1','145','19','24th St / Washington' UNION ALL
SELECT '2019-01-01 07:17:25.0000000','W','1','2','145','18','12th St / Washington' UNION ALL
SELECT '2019-01-01 07:19:58.0000000','W','4','2','145','17','3rd St / Washington' UNION ALL
SELECT '2019-01-01 07:21:57.0000000','W','5','6','145','16','Washington / Central Ave' UNION ALL
SELECT '2019-01-01 07:23:48.0000000','W','5','6','145','15','Van Buren / Central Ave' UNION ALL
SELECT '2019-01-01 07:25:46.0000000','W','0','2','145','14','Roosevelt / Central Ave' UNION ALL
SELECT '2019-01-01 07:28:27.0000000','W','3','1','145','13','McDowell / Central Ave' UNION ALL
SELECT '2019-01-01 07:30:21.0000000','W','0','1','145','12','Encanto / Central Ave' UNION ALL
SELECT '2019-01-01 07:32:07.0000000','W','0','2','145','11','Thomas / Central Ave' UNION ALL
SELECT '2019-01-01 07:33:43.0000000','W','3','0','145','10','Osborn / Central Ave' UNION ALL
SELECT '2019-01-01 07:35:45.0000000','W','1','1','145','9','Indian School / Central Ave' UNION ALL
SELECT '2019-01-01 07:37:22.0000000','W','2','0','145','8','Campbell / Central Ave' UNION ALL
SELECT '2019-01-01 07:39:27.0000000','W','0','0','145','7','Central Ave / Camelback' UNION ALL
SELECT '2019-01-01 07:41:12.0000000','W','0','4','145','6','7th Ave / Camelback' UNION ALL
SELECT '2019-01-01 07:43:58.0000000','W','5','3','145','5','19th Ave / Camelback' UNION ALL
SELECT '2019-01-01 07:47:19.0000000','W','2','6','145','4','Montebello / 19th Ave' UNION ALL
SELECT '2019-01-01 07:50:11.0000000','W','2','5','145','3','Glendale / 19th Ave' UNION ALL
SELECT '2019-01-01 07:52:47.0000000','W','1','5','145','2','Northern / 19th Ave' UNION ALL
SELECT '2019-01-01 07:56:28.0000000','E','8','12','145','1','19th Ave / Dunlap' UNION ALL
SELECT '2019-01-01 08:12:32.0000000','E','0','0','145','2','Northern / 19th Ave' UNION ALL
SELECT '2019-01-01 08:15:09.0000000','E','3','0','145','3','Glendale / 19th Ave' UNION ALL
SELECT '2019-01-01 08:18:02.0000000','E','2','0','145','4','Montebello / 19th Ave' UNION ALL
SELECT '2019-01-01 08:20:37.0000000','E','13','1','145','5','19th Ave / Camelback' UNION ALL
SELECT '2019-01-01 08:23:03.0000000','E','3','7','145','6','7th Ave / Camelback' UNION ALL
SELECT '2019-01-01 08:24:52.0000000','E','3','3','145','7','Central Ave / Camelback' UNION ALL
SELECT '2019-01-01 08:27:19.0000000','E','1','1','145','8','Campbell / Central Ave' UNION ALL
SELECT '2019-01-01 08:29:06.0000000','E','3','2','145','9','Indian School / Central Ave' UNION ALL
SELECT '2019-01-01 08:31:06.0000000','E','1','3','145','10','Osborn / Central Ave' UNION ALL
SELECT '2019-01-01 08:32:36.0000000','E','1','0','145','11','Thomas / Central Ave' UNION ALL
SELECT '2019-01-01 08:34:56.0000000','E','2','0','145','12','Encanto / Central Ave' UNION ALL
SELECT '2019-01-01 08:36:51.0000000','E','3','0','145','13','McDowell / Central Ave' UNION ALL
SELECT '2019-01-01 08:38:30.0000000','E','8','0','145','14','Roosevelt / Central Ave' UNION ALL
SELECT '2019-01-01 08:40:32.0000000','E','4','5','145','15','Van Buren / 1st Ave' UNION ALL
SELECT '2019-01-01 08:42:28.0000000','E','2','3','145','16','Jefferson / 1st Ave' UNION ALL
SELECT '2019-01-01 08:44:27.0000000','E','1','2','145','17','3rd St / Jefferson' UNION ALL
SELECT '2019-01-01 08:47:05.0000000','E','4','1','145','18','12th St / Jefferson' UNION ALL
SELECT '2019-01-01 08:50:47.0000000','E','3','2','145','19','24th St / Jefferson' UNION ALL
SELECT '2019-01-01 08:54:36.0000000','E','4','2','145','20','38th St / Washington' UNION ALL
SELECT '2019-01-01 08:56:42.0000000','E','5','7','145','21','44th St / Washington' UNION ALL
SELECT '2019-01-01 09:01:01.0000000','E','2','2','145','23','Priest Dr / Washington St' UNION ALL
SELECT '2019-01-01 09:03:39.0000000','E','0','0','145','24','Center Pkwy / Washington' UNION ALL
SELECT '2019-01-01 09:06:46.0000000','E','0','3','145','25','Mill Ave / 3rd St' UNION ALL
SELECT '2019-01-01 09:08:21.0000000','E','0','3','145','26','Veterans Way / College Ave' UNION ALL
SELECT '2019-01-01 09:11:21.0000000','E','2','2','145','28','University Dr / Rural Rd' UNION ALL
SELECT '2019-01-01 09:14:08.0000000','E','0','7','145','27','Dorsey Ln / Apache Blvd' UNION ALL
SELECT '2019-01-01 09:15:59.0000000','E','2','1','145','29','McClintock Dr / Apache Blvd' UNION ALL
SELECT '2019-01-01 09:17:45.0000000','E','0','0','145','30','Smith-Martin / Apache Blvd' UNION ALL
SELECT '2019-01-01 09:20:26.0000000','E','1','0','145','31','Price-101 / Apache Blvd' UNION ALL
SELECT '2019-01-01 09:23:14.0000000','E','0','3','145','32','Sycamore / Main St' UNION ALL
SELECT '2019-01-01 09:25:46.0000000','E','2','5','145','33','Alma School / Main St' UNION ALL
SELECT '2019-01-01 09:28:29.0000000','E','0','7','145','34','Country Club / Main St' UNION ALL
SELECT '2019-01-01 09:30:30.0000000','E','2','3','145','35','Center / Main St' UNION ALL
SELECT '2019-01-01 09:32:56.0000000','W','20','21','145','36','Mesa Dr / Main St' UNION ALL
SELECT '2019-01-01 09:50:46.0000000','W','0','0','145','35','Center / Main St' UNION ALL
SELECT '2019-01-01 09:53:10.0000000','W','3','0','145','34','Country Club / Main St' UNION ALL
SELECT '2019-01-01 09:56:44.0000000','W','6','5','145','33','Alma School / Main St' UNION ALL
SELECT '2019-01-01 09:59:14.0000000','W','4','1','145','32','Sycamore / Main St'

-- Set Identity insert back to normal
SET Identity_Insert #testSTationSum OFF

I'm still working on parsing the individual station's daily ridership based on direction (StationName, day (arrival) and the direction). I am hoping that doing it for every car for every station over the course of a year (each table is stored by year) and be brought in later, as long as I'm getting the correct numbers over a day's worth of information. I have the following SQL going for that, but it's not taking into account that the direction will change over a day, and that each time it changes direction a new summation will need to occur.

Also, based on issues with the collection (whether by people swiping their passes or by Automated People Counters) the total boardings (totalIn) never equals the alightings (totalOut). With that in mind I need each day to start at 0, and then reset back to 0 when a direction change occurs.

This is the direction I am working in so far, for the daily summation by Station:

SELECT Car, Direction, StationName, arrival, totalIn, totalOut,
  SUM(totalIn - totalOut) OVER (PARTITION BY Car, Direction, StationName ORDER BY Car, arrival, Direction) 
  AS StationRiders
FROM testSTationSum

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Your SQL looks a bit odd. There is no point in ordering by something you are partitioning by. You have Car, Direction in the partition and also in the order by.

I'm not exactly sure what you are trying to do but I think this might be more like what you want:

SELECT Car, Direction, StationName, arrival, totalIn, totalOut,
  SUM(totalIn - totalOut) OVER (PARTITION BY Car, convert(varchar, arrival,112) ORDER BY arrival) 
  AS StationRiders
  FROM #testSTationSum
 ORDER BY Car, arrival
;

Are you just trying to get the number of passengers in a car at any time?

Do any passengers stay in a car as it changes direction or do they all get out and new ones get in?

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

There are a couple of issues with your script.

You don't actually insert the data into the table.
You specify identity insert on, but you haven't included the identity, so it errors out.

I also prefer using a table value constructor to a series of SELECT/UNION ALL combos.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

jwilkerson@valleymetro.org SSC Enthusiast Points: 187 More actions · Answer 6

Thank you for replying Jonathan, and I see your point about the ordering. I am still trying to discover what might work so I had left some things in the order by not caring about the performance, just trying to discover what might work.

I tried your query and it works to get the ridership in 1 car for each of the stops, which answers your first question. Yes, I am interested in determining the ridership in each of the stops. There should never be a negative ridership, so whenever that happens, I want to reset it to 0. I think this can be accomplished using a common table expression, as shown below.

To answer your second question, no, the passengers do not stay in the car as it changes direction. They are all told to get off and we restart the count as they get back in (usually to another car that is already waiting in the new direction). This is my biggest struggle so far, how to reset the count back to zer0 as the Direction changes throughout the day.

So, using what you have discussed so far, a solution might be to use a CTE to clean up the negative numbers, as in:

;WITH cteRidersDaily AS (
SELECT Car, Direction, StationName, arrival, totalIn, totalOut,
SUM(totalIn - totalOut) OVER (PARTITION BY Car, convert(varchar, arrival,112) ORDER BY arrival)
AS ridersTest
FROM testSTationSum
)
SELECT Car, Direction, StationName, arrival, totalIn, totalOut,
CASE WHEN ridersTest > 0 THEN ridersTest
ELSE 0
END
AS StationRiders
FROM cteRidersDaily
ORDER BY Car, arrival

And to work with the multiple cars, maybe using your previous CTE as a front end, like this:

;WITH CTE AS
(
SELECT DISTINCT ArriveDate, Car
FROM testSTationSum
)
SELECT CTE.ArriveDate, CTE.Car, x.arrival, x.direction, x.totalIn, x.totalOut, x.StationId, x.StationName
, SUM(totalIn - totalOut) OVER (PARTITION BY x.Car, convert(varchar, arrival,112) ORDER BY arrival) as testRiders
FROM testSTationSum x
INNER JOIN CTE ON CTE.ArriveDate = x.ArriveDate
AND CTE.Car = x.Car
ORDER BY CTE.ArriveDate, CTE.car, x.arrival
;

But it's still not handling the direction change (reset to zero) and the negative ridership needs to reset to zero.

jwilkerson@valleymetro.org SSC Enthusiast Points: 187 More actions · Answer 7

Here's a revised test data load script (with more cars), still using the 'Select/Union all' but should be working:

-- Remove the table if it already exists
IF OBJECT_ID('TempDB..#testStationSum', 'U') IS NOT NULL
DROP TABLE #testStationSum

-- Create the Test Table
CREATE TABLE [dbo].[#testStationSum](
[ID] [int] IDENTITY(1,1) NOT NULL,
[arrival] [datetime2](7) NULL,
[direction] [nvarchar](6) NULL,
[totalIn] [int] NULL,
[totalOut] [int] NULL,
[Car] [nvarchar](10) NULL,
[StationId] [int] NULL,
[StationName] [nvarchar](255) NULL,
[ArriveDate] [nvarchar](255) NULL
) ON [PRIMARY]

-- Special required conditions
SET DATEFORMAT MDY

-- Insert the test data
INSERT INTO [dbo].[#testStationSum] ([arrival],[direction],[totalIn],[totalOut],[Car],[StationId],[StationName],[ArriveDate])
SELECT '2019-01-01 00:01:24.0000000','W','0','1','144','26','Veterans Way / College Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 00:01:48.0000000','W','1','2','146','33','Alma School / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 00:03:21.0000000','W','0','0','144','25','Mill Ave / 3rd St','01/01/2019' UNION ALL
SELECT '2019-01-01 00:04:21.0000000','W','0','0','146','32','Sycamore / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 00:06:54.0000000','W','0','0','144','24','Center Pkwy / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 00:07:40.0000000','W','0','1','146','31','Price-101 / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 00:08:43.0000000','W','3','3','144','23','Priest Dr / Washington St','01/01/2019' UNION ALL
SELECT '2019-01-01 00:10:07.0000000','W','0','0','146','30','Smith-Martin / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 00:11:28.0000000','W','0','0','146','29','McClintock Dr / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 00:13:15.0000000','W','0','0','146','27','Dorsey Ln / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 00:15:54.0000000','W','0','0','146','28','University Dr / Rural Rd','01/01/2019' UNION ALL
SELECT '2019-01-01 00:19:04.0000000','W','0','0','146','26','Veterans Way / College Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 00:20:43.0000000','W','12','2','146','25','Mill Ave / 3rd St','01/01/2019' UNION ALL
SELECT '2019-01-01 00:23:40.0000000','W','2','1','146','24','Center Pkwy / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 00:25:51.0000000','W','4','25','146','23','Priest Dr / Washington St','01/01/2019' UNION ALL
SELECT '2019-01-01 03:53:47.0000000','W','2','0','145','21','44th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 03:56:03.0000000','W','5','3','145','20','38th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 03:59:53.0000000','W','1','0','145','19','24th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 04:04:09.0000000','W','2','0','145','18','12th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 04:07:11.0000000','W','4','1','145','17','3rd St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 04:09:58.0000000','W','1','7','145','16','Washington / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:11:47.0000000','W','4','3','145','15','Van Buren / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:13:51.0000000','W','4','3','145','14','Roosevelt / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:15:27.0000000','W','1','1','145','13','McDowell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:17:20.0000000','W','2','1','145','12','Encanto / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:19:05.0000000','W','2','4','145','11','Thomas / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:20:51.0000000','W','3','2','145','10','Osborn / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:22:57.0000000','W','3','3','145','9','Indian School / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:24:29.0000000','W','3','1','145','8','Campbell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:26:40.0000000','W','2','2','145','7','Central Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 04:28:28.0000000','W','2','2','145','6','7th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 04:31:00.0000000','W','2','4','145','5','19th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 04:34:00.0000000','W','0','0','144','21','44th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 04:34:28.0000000','W','2','4','145','4','Montebello / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:35:55.0000000','W','3','1','144','20','38th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 04:37:39.0000000','W','2','3','145','3','Glendale / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:39:24.0000000','W','0','0','144','19','24th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 04:40:45.0000000','W','2','1','145','2','Northern / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:43:09.0000000','W','2','0','144','18','12th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 04:44:30.0000000','E','14','13','145','1','19th Ave / Dunlap','01/01/2019' UNION ALL
SELECT '2019-01-01 04:45:52.0000000','W','0','0','144','17','3rd St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 04:48:04.0000000','W','0','0','144','16','Washington / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:50:00.0000000','W','1','0','144','15','Van Buren / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:52:21.0000000','W','0','0','144','14','Roosevelt / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:53:56.0000000','W','1','2','144','13','McDowell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:53:58.0000000','E','1','0','145','2','Northern / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:55:48.0000000','W','0','0','144','12','Encanto / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:56:50.0000000','E','3','0','145','3','Glendale / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:57:32.0000000','W','0','2','144','11','Thomas / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 04:59:00.0000000','W','0','0','144','10','Osborn / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:00:14.0000000','E','4','4','145','4','Montebello / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:00:55.0000000','W','0','0','144','9','Indian School / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:01:09.0000000','E','1','0','146','23','Priest Dr / Washington St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:02:29.0000000','W','0','0','144','8','Campbell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:04:35.0000000','W','0','0','144','7','Central Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 05:06:26.0000000','W','0','0','144','6','7th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 05:07:38.0000000','E','1','2','145','5','19th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 05:08:42.0000000','E','0','0','146','24','Center Pkwy / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 05:08:47.0000000','W','0','0','144','5','19th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 05:10:08.0000000','E','0','0','145','6','7th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 05:11:49.0000000','E','0','0','146','25','Mill Ave / 3rd St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:11:53.0000000','E','1','0','145','7','Central Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 05:12:00.0000000','W','0','0','144','4','Montebello / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:13:26.0000000','E','0','1','146','26','Veterans Way / College Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:13:57.0000000','E','0','0','145','8','Campbell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:15:00.0000000','W','0','0','144','3','Glendale / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:15:25.0000000','E','2','0','145','9','Indian School / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:17:27.0000000','E','0','1','145','10','Osborn / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:17:30.0000000','E','1','0','146','28','University Dr / Rural Rd','01/01/2019' UNION ALL
SELECT '2019-01-01 05:17:36.0000000','W','0','0','144','2','Northern / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:19:04.0000000','E','3','2','145','11','Thomas / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:20:37.0000000','E','0','1','146','27','Dorsey Ln / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 05:21:09.0000000','E','0','0','145','12','Encanto / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:21:30.0000000','E','14','4','144','1','19th Ave / Dunlap','01/01/2019' UNION ALL
SELECT '2019-01-01 05:22:26.0000000','E','0','0','146','29','McClintock Dr / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 05:23:08.0000000','E','4','2','145','13','McDowell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:24:07.0000000','E','0','0','146','30','Smith-Martin / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 05:24:40.0000000','E','1','1','145','14','Roosevelt / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:26:20.0000000','E','0','0','146','31','Price-101 / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 05:26:43.0000000','E','2','0','145','15','Van Buren / 1st Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:28:58.0000000','E','0','1','145','16','Jefferson / 1st Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:29:28.0000000','E','0','0','146','32','Sycamore / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:30:58.0000000','E','1','1','145','17','3rd St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 05:32:03.0000000','E','0','0','146','33','Alma School / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:33:11.0000000','E','1','1','144','2','Northern / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:33:42.0000000','E','0','1','145','18','12th St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 05:34:40.0000000','E','0','0','146','34','Country Club / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:35:44.0000000','E','2','0','144','3','Glendale / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:36:52.0000000','E','0','0','146','35','Center / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:37:48.0000000','E','2','1','145','19','24th St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 05:38:27.0000000','E','0','2','144','4','Montebello / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:39:24.0000000','W','1','0','146','36','Mesa Dr / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:40:57.0000000','E','2','1','144','5','19th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 05:41:53.0000000','E','1','0','145','20','38th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 05:43:14.0000000','E','1','1','144','6','7th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 05:44:00.0000000','E','4','14','145','21','44th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 05:45:27.0000000','E','2','0','144','7','Central Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 05:47:32.0000000','E','0','0','144','8','Campbell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:48:33.0000000','E','1','3','145','23','Priest Dr / Washington St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:48:58.0000000','E','1','0','144','9','Indian School / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:50:34.0000000','E','0','1','145','24','Center Pkwy / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 05:50:59.0000000','W','0','0','146','35','Center / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:51:20.0000000','E','0','1','144','10','Osborn / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:52:48.0000000','E','2','2','144','11','Thomas / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:53:02.0000000','W','2','1','146','34','Country Club / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:53:47.0000000','E','1','3','145','25','Mill Ave / 3rd St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:55:24.0000000','E','0','0','144','12','Encanto / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:55:27.0000000','E','2','3','145','26','Veterans Way / College Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:55:45.0000000','W','2','3','146','33','Alma School / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 05:57:19.0000000','E','2','0','144','13','McDowell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:58:22.0000000','E','0','1','145','28','University Dr / Rural Rd','01/01/2019' UNION ALL
SELECT '2019-01-01 05:59:02.0000000','E','1','2','144','14','Roosevelt / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 05:59:17.0000000','W','0','1','146','32','Sycamore / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:01:10.0000000','E','0','0','145','27','Dorsey Ln / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:01:12.0000000','E','2','6','144','15','Van Buren / 1st Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:02:34.0000000','W','2','0','146','31','Price-101 / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:03:15.0000000','E','0','2','145','29','McClintock Dr / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:03:41.0000000','E','0','0','144','16','Jefferson / 1st Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:04:46.0000000','W','0','0','146','30','Smith-Martin / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:05:07.0000000','E','0','0','145','30','Smith-Martin / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:06:23.0000000','W','2','0','146','29','McClintock Dr / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:07:09.0000000','E','0','1','144','17','3rd St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 06:07:25.0000000','E','0','0','145','31','Price-101 / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:08:39.0000000','W','0','0','146','27','Dorsey Ln / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:09:44.0000000','E','2','0','144','18','12th St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 06:10:32.0000000','E','0','0','145','32','Sycamore / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:11:43.0000000','W','1','1','146','28','University Dr / Rural Rd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:13:37.0000000','E','0','1','145','33','Alma School / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:14:07.0000000','E','2','0','144','19','24th St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 06:14:40.0000000','W','1','0','146','26','Veterans Way / College Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:16:14.0000000','E','0','0','145','34','Country Club / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:16:22.0000000','W','3','2','146','25','Mill Ave / 3rd St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:17:55.0000000','E','0','0','144','20','38th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 06:18:12.0000000','E','0','0','145','35','Center / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:19:25.0000000','W','0','0','146','24','Center Pkwy / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 06:19:57.0000000','E','2','6','144','21','44th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 06:20:08.0000000','W','5','1','145','36','Mesa Dr / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:21:19.0000000','W','2','4','146','23','Priest Dr / Washington St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:24:04.0000000','E','0','2','144','23','Priest Dr / Washington St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:25:44.0000000','W','3','4','146','21','44th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 06:26:00.0000000','E','0','0','144','24','Center Pkwy / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 06:27:46.0000000','W','2','2','146','20','38th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 06:29:05.0000000','E','0','2','144','25','Mill Ave / 3rd St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:30:39.0000000','E','3','0','144','26','Veterans Way / College Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:31:16.0000000','W','5','3','145','35','Center / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:32:58.0000000','W','0','4','146','19','24th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 06:33:12.0000000','E','0','1','144','28','University Dr / Rural Rd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:33:39.0000000','W','2','1','145','34','Country Club / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:37:00.0000000','W','0','0','146','18','12th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 06:37:26.0000000','W','2','0','145','33','Alma School / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:39:35.0000000','E','1','0','144','27','Dorsey Ln / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:39:50.0000000','W','4','7','145','32','Sycamore / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:40:30.0000000','W','0','1','146','17','3rd St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 06:41:19.0000000','E','1','0','144','29','McClintock Dr / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:42:28.0000000','W','6','1','145','31','Price-101 / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:42:31.0000000','W','1','1','146','16','Washington / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:42:50.0000000','E','0','1','144','30','Smith-Martin / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:44:44.0000000','W','4','2','145','30','Smith-Martin / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:44:48.0000000','W','1','0','146','15','Van Buren / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:45:20.0000000','E','0','0','144','31','Price-101 / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:46:16.0000000','W','3','3','145','29','McClintock Dr / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:46:52.0000000','W','0','1','146','14','Roosevelt / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:47:35.0000000','E','0','0','144','32','Sycamore / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:48:27.0000000','W','0','2','145','27','Dorsey Ln / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:48:32.0000000','W','1','0','146','13','McDowell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:49:46.0000000','E','0','2','144','33','Alma School / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:50:27.0000000','W','3','2','146','12','Encanto / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:52:00.0000000','E','0','2','144','34','Country Club / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:52:24.0000000','W','4','0','146','11','Thomas / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:53:51.0000000','E','0','0','144','35','Center / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:54:00.0000000','W','0','0','146','10','Osborn / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:54:40.0000000','W','3','0','145','28','University Dr / Rural Rd','01/01/2019' UNION ALL
SELECT '2019-01-01 06:55:42.0000000','E','5','12','144','36','Mesa Dr / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:56:19.0000000','W','0','1','146','9','Indian School / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:57:36.0000000','W','0','4','145','26','Veterans Way / College Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:57:48.0000000','W','0','1','146','8','Campbell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 06:59:19.0000000','W','5','3','145','25','Mill Ave / 3rd St','01/01/2019' UNION ALL
SELECT '2019-01-01 06:59:54.0000000','W','2','1','146','7','Central Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 07:02:08.0000000','W','0','1','145','24','Center Pkwy / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 07:02:11.0000000','W','0','1','146','6','7th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 07:02:49.0000000','W','1','2','144','36','Mesa Dr / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 07:03:58.0000000','W','3','1','145','23','Priest Dr / Washington St','01/01/2019' UNION ALL
SELECT '2019-01-01 07:04:31.0000000','W','3','3','146','5','19th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 07:07:33.0000000','W','0','2','146','4','Montebello / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:08:02.0000000','W','5','5','145','21','44th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 07:10:06.0000000','W','3','2','145','20','38th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 07:10:14.0000000','W','3','5','146','3','Glendale / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:12:55.0000000','W','2','5','146','2','Northern / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:13:44.0000000','W','1','1','145','19','24th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 07:16:10.0000000','E','5','11','146','1','19th Ave / Dunlap','01/01/2019' UNION ALL
SELECT '2019-01-01 07:17:25.0000000','W','1','2','145','18','12th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 07:19:58.0000000','W','4','2','145','17','3rd St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 07:21:57.0000000','W','5','6','145','16','Washington / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:23:48.0000000','W','5','6','145','15','Van Buren / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:25:46.0000000','W','0','2','145','14','Roosevelt / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:28:27.0000000','W','3','1','145','13','McDowell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:30:21.0000000','W','0','1','145','12','Encanto / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:32:07.0000000','W','0','2','145','11','Thomas / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:32:28.0000000','E','9','2','146','2','Northern / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:33:43.0000000','W','3','0','145','10','Osborn / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:34:56.0000000','E','4','0','146','3','Glendale / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:35:45.0000000','W','1','1','145','9','Indian School / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:37:22.0000000','W','2','0','145','8','Campbell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:37:37.0000000','E','1','2','146','4','Montebello / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:39:27.0000000','W','0','0','145','7','Central Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 07:40:04.0000000','E','3','1','146','5','19th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 07:41:12.0000000','W','0','4','145','6','7th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 07:42:27.0000000','E','5','3','146','6','7th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 07:43:58.0000000','W','5','3','145','5','19th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 07:44:10.0000000','E','0','4','146','7','Central Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 07:46:25.0000000','E','0','2','146','8','Campbell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:47:19.0000000','W','2','6','145','4','Montebello / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:47:53.0000000','E','6','6','146','9','Indian School / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:50:11.0000000','W','2','5','145','3','Glendale / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:52:47.0000000','W','1','5','145','2','Northern / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:53:08.0000000','E','0','0','146','10','Osborn / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:54:43.0000000','E','1','0','146','11','Thomas / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:56:21.0000000','E','1','1','146','12','Encanto / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 07:56:28.0000000','E','8','12','145','1','19th Ave / Dunlap','01/01/2019' UNION ALL
SELECT '2019-01-01 07:58:12.0000000','E','1','4','146','13','McDowell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:00:00.0000000','E','0','0','146','14','Roosevelt / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:02:28.0000000','E','2','3','146','15','Van Buren / 1st Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:03:54.0000000','E','4','8','146','16','Jefferson / 1st Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:06:45.0000000','E','6','2','146','17','3rd St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 08:09:51.0000000','E','1','1','146','18','12th St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 08:12:32.0000000','E','0','0','145','2','Northern / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:14:07.0000000','E','3','4','146','19','24th St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 08:15:09.0000000','E','3','0','145','3','Glendale / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:17:40.0000000','E','3','1','146','20','38th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 08:18:02.0000000','E','2','0','145','4','Montebello / 19th Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:19:33.0000000','E','2','7','146','21','44th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 08:20:37.0000000','E','13','1','145','5','19th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 08:23:03.0000000','E','3','7','145','6','7th Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 08:23:46.0000000','E','1','0','146','23','Priest Dr / Washington St','01/01/2019' UNION ALL
SELECT '2019-01-01 08:24:52.0000000','E','3','3','145','7','Central Ave / Camelback','01/01/2019' UNION ALL
SELECT '2019-01-01 08:26:41.0000000','E','1','1','146','24','Center Pkwy / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 08:27:19.0000000','E','1','1','145','8','Campbell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:29:06.0000000','E','3','2','145','9','Indian School / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:29:44.0000000','E','0','1','146','25','Mill Ave / 3rd St','01/01/2019' UNION ALL
SELECT '2019-01-01 08:31:06.0000000','E','1','3','145','10','Osborn / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:31:13.0000000','E','3','4','146','26','Veterans Way / College Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:32:36.0000000','E','1','0','145','11','Thomas / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:33:54.0000000','E','0','1','146','28','University Dr / Rural Rd','01/01/2019' UNION ALL
SELECT '2019-01-01 08:34:56.0000000','E','2','0','145','12','Encanto / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:36:51.0000000','E','3','0','145','13','McDowell / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:37:34.0000000','E','0','0','146','27','Dorsey Ln / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 08:38:30.0000000','E','8','0','145','14','Roosevelt / Central Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:39:21.0000000','E','2','6','146','29','McClintock Dr / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 08:40:32.0000000','E','4','5','145','15','Van Buren / 1st Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:40:57.0000000','E','0','0','146','30','Smith-Martin / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 08:42:28.0000000','E','2','3','145','16','Jefferson / 1st Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 08:43:30.0000000','E','3','1','146','31','Price-101 / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 08:44:27.0000000','E','1','2','145','17','3rd St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 08:46:00.0000000','E','0','2','146','32','Sycamore / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 08:47:05.0000000','E','4','1','145','18','12th St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 08:48:19.0000000','E','3','2','146','33','Alma School / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 08:50:47.0000000','E','3','2','145','19','24th St / Jefferson','01/01/2019' UNION ALL
SELECT '2019-01-01 08:51:54.0000000','E','1','10','146','34','Country Club / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 08:53:35.0000000','E','1','2','146','35','Center / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 08:54:36.0000000','E','4','2','145','20','38th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 08:55:42.0000000','W','12','11','146','36','Mesa Dr / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 08:56:42.0000000','E','5','7','145','21','44th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 09:01:01.0000000','E','2','2','145','23','Priest Dr / Washington St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:03:39.0000000','E','0','0','145','24','Center Pkwy / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 09:06:46.0000000','E','0','3','145','25','Mill Ave / 3rd St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:08:21.0000000','E','0','3','145','26','Veterans Way / College Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 09:10:58.0000000','W','0','2','146','35','Center / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:11:21.0000000','E','2','2','145','28','University Dr / Rural Rd','01/01/2019' UNION ALL
SELECT '2019-01-01 09:13:30.0000000','W','8','2','146','34','Country Club / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:14:08.0000000','E','0','7','145','27','Dorsey Ln / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 09:15:59.0000000','E','2','1','145','29','McClintock Dr / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 09:16:16.0000000','W','4','5','146','33','Alma School / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:17:45.0000000','E','0','0','145','30','Smith-Martin / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 09:20:26.0000000','E','1','0','145','31','Price-101 / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 09:21:12.0000000','W','4','2','146','32','Sycamore / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:23:14.0000000','E','0','3','145','32','Sycamore / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:24:21.0000000','W','2','1','146','31','Price-101 / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 09:25:46.0000000','E','2','5','145','33','Alma School / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:26:23.0000000','W','0','0','146','30','Smith-Martin / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 09:27:51.0000000','W','3','3','146','29','McClintock Dr / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 09:28:29.0000000','E','0','7','145','34','Country Club / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:29:19.0000000','W','2','1','146','27','Dorsey Ln / Apache Blvd','01/01/2019' UNION ALL
SELECT '2019-01-01 09:30:30.0000000','E','2','3','145','35','Center / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:32:16.0000000','W','0','4','146','28','University Dr / Rural Rd','01/01/2019' UNION ALL
SELECT '2019-01-01 09:32:56.0000000','W','20','21','145','36','Mesa Dr / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:34:53.0000000','W','1','4','146','26','Veterans Way / College Ave','01/01/2019' UNION ALL
SELECT '2019-01-01 09:36:25.0000000','W','3','1','146','25','Mill Ave / 3rd St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:39:14.0000000','W','0','0','146','24','Center Pkwy / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 09:41:03.0000000','W','0','1','146','23','Priest Dr / Washington St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:45:13.0000000','W','2','5','146','21','44th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 09:47:09.0000000','W','0','0','146','20','38th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 09:50:46.0000000','W','3','0','146','19','24th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 09:50:46.0000000','W','0','0','145','35','Center / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:53:10.0000000','W','3','0','145','34','Country Club / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:55:04.0000000','W','0','1','146','18','12th St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 09:56:44.0000000','W','6','5','145','33','Alma School / Main St','01/01/2019' UNION ALL
SELECT '2019-01-01 09:57:37.0000000','W','0','1','146','17','3rd St / Washington','01/01/2019' UNION ALL
SELECT '2019-01-01 09:59:14.0000000','W','4','1','145','32','Sycamore / Main St','01/01/2019'

SELECT top(10) * FROM #testStationSum

This reply was modified 1 year, 5 months ago by jwilkerson@valleymetro.org.

jwilkerson@valleymetro.org SSC Enthusiast Points: 187 More actions · Answer 8

Drew, thank you for your reply. Your method seems more like what I was looking for. I modified it just a little by adding your final select as a last CTE so that I could clean up any negative values ( and leave them as zeros). I think adding the CTE by Jonathan AC Roberts to perform this for all cars over all days might finish this thing off.

So far, this is all that I've done with what you provided, again, to get rid of negative numbers.

WITH Reversals AS
(
    SELECT s.ID
 , s.arrival
 , s.direction
 , s.totalIn
 , s.totalOut
 , s.Car
 , s.StationId
 , s.StationName
         , CAST(s.arrival AS DATE) AS arrival_dt
         , CASE WHEN s.direction = LAG(s.direction) OVER(PARTITION BY CAST(s.arrival AS DATE), s.Car ORDER BY s.arrival)
                THEN 0
                ELSE 1
           END AS is_reversal
    FROM testStationSum AS s
)
, Trips AS
(
    SELECT r.ID
     , r.arrival
         , r.arrival_dt
     , r.direction
     , r.totalIn
     , r.totalOut
     , r.Car
     , r.StationId
     , r.StationName
     , SUM(r.is_reversal) OVER(PARTITION BY r.arrival_dt, r.Car ORDER BY r.arrival ROWS UNBOUNDED PRECEDING) AS Trip_Grp
    FROM Reversals AS r
)
, ridersTest AS (
SELECT t.Car
 , t.direction
 , t.StationName
 , t.arrival
 , t.totalIn
 , t.totalOut
 , SUM(t.totalIn - t.totalOut) OVER(PARTITION BY t.arrival_dt, t.Car, t.Trip_Grp ORDER BY t.arrival) AS testRiders
FROM Trips AS t
)
SELECT Car, direction, StationName, arrival, totalIn, totalOut
, CASE WHEN testRiders < 0 THEN 0
ELSE testRiders
END
AS CarRiders
FROM ridersTest

jwilkerson@valleymetro.org SSC Enthusiast Points: 187 More actions · Answer 9

After looking at Drew.Allen's solution, I realize that it works just as well for multiple cars over multiple days, so that is what I was looking for. We are trying to determine if the ridership between stations is getting close to car capacity, so knowing what it is based on the boardings and alightings is the first step in analyzing this.

Thank you Drew.Allen for your help and solution, and thank you Jonathan AC Roberts for your help and guidance. I really appreciate it and it makes sense now.

jschmidt 17654 SSCommitted Points: 1656 More actions · Answer 10

jwilkerson@valleymetro.org wrote:

Drew, thank you for your reply. Your method seems more like what I was looking for. I modified it just a little by adding your final select as a last CTE so that I could clean up any negative values ( and leave them as zeros). I think adding the CTE by Jonathan AC Roberts to perform this for all cars over all days might finish this thing off.

So far, this is all that I've done with what you provided, again, to get rid of negative numbers.

WITH Reversals AS
(
    SELECT s.ID
 , s.arrival
 , s.direction
 , s.totalIn
 , s.totalOut
 , s.Car
 , s.StationId
 , s.StationName
         , CAST(s.arrival AS DATE) AS arrival_dt
         , CASE WHEN s.direction = LAG(s.direction) OVER(PARTITION BY CAST(s.arrival AS DATE), s.Car ORDER BY s.arrival)
                THEN 0
                ELSE 1
           END AS is_reversal
    FROM testStationSum AS s
)
, Trips AS
(
    SELECT r.ID
     , r.arrival
         , r.arrival_dt
     , r.direction
     , r.totalIn
     , r.totalOut
     , r.Car
     , r.StationId
     , r.StationName
     , SUM(r.is_reversal) OVER(PARTITION BY r.arrival_dt, r.Car ORDER BY r.arrival ROWS UNBOUNDED PRECEDING) AS Trip_Grp
    FROM Reversals AS r
)
, ridersTest AS (
SELECT t.Car
 , t.direction
 , t.StationName
 , t.arrival
 , t.totalIn
 , t.totalOut
 , SUM(t.totalIn - t.totalOut) OVER(PARTITION BY t.arrival_dt, t.Car, t.Trip_Grp ORDER BY t.arrival) AS testRiders
FROM Trips AS t
)
SELECT Car, direction, StationName, arrival, totalIn, totalOut
, CASE WHEN testRiders < 0 THEN 0
ELSE testRiders
END
AS CarRiders
FROM ridersTest

In your first train group 144/W, it starts with a -1 for testRiders, which is the base for all of the following station totals, so your first group is off by 1 rider at each station. Note the 6th record with a 3 on 1 off that shows 1 rider. If any train group started with a larger negative number, then it could be off significantly.

Untitled

I haven't figured out the interim query need to handle the negative numbers after a reversal, but also think the running total of CarRiders would be affected by negative testRiders numbers wherever they appeared. I'll keep exploring.

jschmidt 17654 SSCommitted Points: 1656 More actions · Answer 11

Found a useful tutorial at https://www.youtube.com/watch?v=GlutgLKmyn4

Resets the running total when it becomes negative. I think this works. I added a couple fields to the ridersTest cte, grouped the last query into a riders_updated cte, and applied the update to CarRiders in the last query.

Let me know if you find faults with this. I enjoy the learning challenges.

WITH Reversals AS
(
    SELECT s.ID
 , s.arrival
 , s.direction
 , s.totalIn
 , s.totalOut
 , s.Car
 , s.StationId
 , s.StationName
         , CAST(s.arrival AS DATE) AS arrival_dt
         , CASE WHEN s.direction = LAG(s.direction) OVER(PARTITION BY CAST(s.arrival AS DATE), s.Car ORDER BY s.arrival)
                THEN 0
                ELSE 1
           END AS is_reversal
    FROM #testStationSum AS s
)
, Trips AS
(
    SELECT r.ID
     , r.arrival
         , r.arrival_dt
     , r.direction
     , r.totalIn
     , r.totalOut
     , r.Car
     , r.StationId
     , r.StationName
     , SUM(r.is_reversal) OVER(PARTITION BY r.arrival_dt, r.Car ORDER BY r.arrival ROWS UNBOUNDED PRECEDING) AS Trip_Grp
    FROM Reversals AS r
)
, ridersTest AS (
SELECT t.Car
 , t.arrival_dt
 , t.Trip_Grp
 , t.direction
 , t.StationName
 , t.arrival
 , t.totalIn
 , t.totalOut
 , SUM(t.totalIn - t.totalOut) OVER(PARTITION BY t.arrival_dt, t.Car, t.Trip_Grp ORDER BY t.arrival) AS testRiders
FROM Trips AS t
)
, riders_updated as
(
SELECT *-- Car, direction, StationName, arrival, totalIn, totalOut
, CASE WHEN testRiders < 0 THEN 0
ELSE testRiders
END
AS CarRiders
 , MIN(testRiders) OVER(PARTITION BY rt.arrival_dt, rt.Car, rt.Trip_Grp ORDER BY rt.arrival) AS mintestRiders
FROM ridersTest rt
)
select *
, testRiders + IIF(mintestRiders < 0, -mintestRiders, 0) as updated_CarRiders
from riders_updated