Subtract one column from another and get running total SQL Server 2008 R2

Question

I hate to ask a question that it seems like the answer already exists somewhere, but I've been working through various articles over the last couple days (https://www.sqlshack.com/running-running-totals-sql-server/ , Calculate a Running Total in SQL Server, https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017 etc.) with minimal progress.

It seems most of the examples given provide a way to join (a self join) or some other WHERE clause to narrow the result set? Anyway, I have a table Location that has columns for PassengersOn and PassengersOff; I would like to be able to calculate the running total of passengers on board at a given time so that my resulting table might look something like this

Which shows a running total (OnBoard) for passengers after each location.

Also, I am aware of the OVER clause available in SQL Server 2012, but unfortunately I'm using 2008 R2.

Again sorry for what may be a duplicate question, but I don't see how I can limit my result set based on a join or WHERE clause since I don't have an incrementing column in my "location" table, instead it uses a guid.

EDIT: here is a sample of the table information

CREATE TABLE Query_8v2 (
    [IDStopEvent] NVARCHAR(36),
    [LocationID] NVARCHAR(6),
    [LocName] NVARCHAR(5),
    [PassOn] INT,
    [PassOff] INT
);
INSERT INTO Query_8v2 VALUES
    (N'f00e6b5b-eb64-4e6b-8b87-0000a539ee36',N'guid1',N'Loc01',0,0),
    (N'617cbcae-b467-4adb-b994-00015bca9bb5',N'guid1',N'Loc01',0,59),
    (N'215f92bc-8114-4dd0-a1e1-00016e4f0546',N'guid1',N'Loc01',0,42),
    (N'e8eaaed5-dc0c-48a9-b39b-0001fc44576e',N'guid1',N'Loc01',0,0),
    (N'4c54eef6-11f3-4114-ad9d-0004b1b3849d',N'guid1',N'Loc01',0,0),
    (N'a29eb925-8226-4d89-8760-00063d64067a',N'guid1',N'Loc01',69,0),
    (N'b16e1b1f-d481-447e-9771-000890fe6999',N'guid1',N'Loc01',0,69),
    (N'4f5894ee-a246-4c9d-bc28-0008bc1b3614',N'guid1',N'Loc01',0,44),
    (N'52e447cf-f900-4e49-94ca-0008c262a173',N'guid1',N'Loc01',0,0),
    (N'f120f646-17f2-4bbb-879d-00091665ec7e',N'guid1',N'Loc01',0,0),
    (N'3bbe56e0-c54c-4f3c-9f29-000c914cd724',N'guid1',N'Loc01',32,0),
    (N'1ddda821-23f5-43a5-a86c-000d46d4cdc9',N'guid1',N'Loc01',0,0),
    (N'b58dac6b-6cac-4bf3-af47-000e67b67582',N'guid1',N'Loc01',0,0),
    (N'c9d52156-cc88-4c3c-9409-00103ba9afaa',N'guid1',N'Loc01',0,0),
    (N'662d3006-938d-4a66-8999-00104632991b',N'guid1',N'Loc01',0,106),
    (N'598d135b-3bdb-4d4b-9464-0010ab22b9eb',N'guid1',N'Loc01',0,0),
    (N'c60e2801-efb8-41c3-9dad-00110aae0f2d',N'guid1',N'Loc01',0,0),
    (N'72384001-56a3-413c-a847-0011125a5e31',N'guid1',N'Loc01',0,0),
    (N'081a9c68-514a-4622-ab0d-00117909d029',N'guid1',N'Loc01',0,0),
    (N'afac2c83-ee2e-4b79-8d0b-0011adc313e0',N'guid1',N'Loc01',0,0),
    (N'a0f65fe9-79d2-470e-9885-000acccbf82f',N'guid2',N'Loc02',0,0),
    (N'bd4371c6-896a-4a4c-9168-000b6e3d2bdd',N'guid2',N'Loc02',0,34),
    (N'7c747187-905d-48f5-b9fd-000e233e2986',N'guid2',N'Loc02',21,0),
    (N'a3e2773a-2310-4185-9b0c-00013204c0d4',N'guid3',N'Loc03',0,206),
    (N'1a8e4c21-0550-411f-91ae-00018234e33d',N'guid3',N'Loc03',323,0),
    (N'66ac5d5c-ef97-4041-92cb-0009412a4cec',N'guid3',N'Loc03',0,249),
    (N'5b6b2d10-70e4-4953-bf4b-00099ffbc1cd',N'guid3',N'Loc03',183,0),
    (N'0107bfcb-9628-42f3-8a4d-000bd42d8cff',N'guid3',N'Loc03',0,400),
    (N'f4179bce-399a-417f-bcb1-000fce5ff5b1',N'guid3',N'Loc03',319,0),
    (N'f3668d7f-4338-4c15-bb65-0000f5f6af85',N'guid4',N'Loc04',25,32),
    (N'dad5af74-a873-46ff-8b61-0002a122850a',N'guid4',N'Loc04',19,75),
    (N'e20b705a-6416-4876-aa96-0005e8e25d94',N'guid4',N'Loc04',48,40),
    (N'2e3f93d1-65fa-4b13-a8db-0007e6e47b4a',N'guid4',N'Loc04',48,37),
    (N'7bc78967-ef77-4fb7-a74d-0008dd88268a',N'guid4',N'Loc04',51,42),
    (N'f409014f-189e-4e24-943b-00095acd2e38',N'guid4',N'Loc04',48,71),
    (N'e9a6a04d-32da-45e6-a93b-000ae35cd97b',N'guid4',N'Loc04',63,13),
    (N'5d719c25-8a20-4cce-85a2-000f6be996ba',N'guid4',N'Loc04',57,69),
    (N'5d5a3666-a996-4220-b943-00110f627aee',N'guid4',N'Loc04',27,63),
    (N'941880b8-0873-40ee-936b-0001b711fbba',N'guid5',N'Loc05',55,182),
    (N'f3f360a1-3767-443e-ac19-000878a505eb',N'guid5',N'Loc05',62,41),
    (N'd03d154b-ade6-4c06-af11-000b9fbcb218',N'guid5',N'Loc05',109,86),
    (N'7c296996-32a5-46c5-bafd-000e49bf18ba',N'guid5',N'Loc05',126,68),
    (N'72424ac3-7b47-44f2-9ffa-0003521bf7c2',N'guid6',N'Loc06',3,3),
    (N'abb66bf1-9dab-4f56-a14c-00049b102c9c',N'guid6',N'Loc06',18,38),
    (N'2db22514-3a92-4781-9232-000a6d701063',N'guid6',N'Loc06',88,34),
    (N'c83239ba-4467-4d8d-9bb6-000b0c802255',N'guid6',N'Loc06',13,13),
    (N'32649da2-bd02-44c3-af3a-000d33087fbe',N'guid6',N'Loc06',7,18),
    (N'db9f9f3b-f4f0-4300-85c4-000f09011b60',N'guid6',N'Loc06',3,39),
    (N'e6aa3c22-489d-4f97-b718-0002071629f1',N'guid7',N'Loc07',55,23),
    (N'e648fff9-50ed-42a3-82e4-00027f22287f',N'guid7',N'Loc07',4,28),
    (N'7b157c82-1819-4990-8147-0007f4dcaed6',N'guid7',N'Loc07',8,62),
    (N'3ffecbf1-bd09-4ef8-b17f-00092211960b',N'guid7',N'Loc07',55,29),
    (N'16eab156-126d-440d-a01b-0009a506e922',N'guid7',N'Loc07',3,23),
    (N'69af7b49-ce4e-446c-9947-000a42bffa23',N'guid7',N'Loc07',7,8),
    (N'd0ba9ab8-80dc-47c9-9f61-000e15b8c049',N'guid7',N'Loc07',3,69),
    (N'77749016-19be-4657-b2d5-0005f60f5b5f',N'guid8',N'Loc08',0,163),
    (N'7908e6ae-71be-4f3e-aa77-00078b16dbac',N'guid8',N'Loc08',201,0),
    (N'10f13d13-9a5c-4ef8-960e-00084b5fa97c',N'guid8',N'Loc08',99,1),
    (N'859c00b3-c907-4d90-92de-000e2b7f95d8',N'guid8',N'Loc08',2,167),
    (N'e00136e2-e71e-4aed-afbf-00005f66f1b6',N'guid9',N'Loc09',0,299),
    (N'ab711e41-e6e3-45b3-ad18-000597d39430',N'guid9',N'Loc09',0,158),
    (N'301fada9-f0c1-4afb-aaf2-0005a7d0b3e8',N'guid9',N'Loc09',137,0),
    (N'67d1a3f1-547d-495e-98c1-00080e3309b6',N'guid9',N'Loc09',67,0),
    (N'a71a4103-dffc-40da-92b8-000a987987a2',N'guid9',N'Loc09',124,0),
    (N'a60f9e16-e262-404e-9947-0000732dded4',N'guid10',N'Loc10',0,103),
    (N'e4aab4d3-9c58-49fb-a9d7-0001350c9e74',N'guid10',N'Loc10',0,0),
    (N'5e8617c7-d2c8-4fb4-b745-0001f8eac18a',N'guid10',N'Loc10',96,0),
    (N'1864b5e5-fdda-4f9b-9522-0002e2afee4c',N'guid10',N'Loc10',0,59),
    (N'05a93b5f-7776-437c-87b8-000314a9202c',N'guid10',N'Loc10',0,87),
    (N'f0d6c884-e906-4aa0-8d01-00034d8d0ea3',N'guid10',N'Loc10',0,0),
    (N'0f8c0751-92ed-445e-9bfc-000416967ce6',N'guid10',N'Loc10',0,0),
    (N'5733564d-cbeb-4072-bcb5-0004ad90ffc6',N'guid10',N'Loc10',64,0),
    (N'bf3209a9-bbb4-4aa2-8463-0006702865a4',N'guid10',N'Loc10',72,0),
    (N'289647e7-7de0-482c-8771-00088940f560',N'guid10',N'Loc10',0,0),
    (N'1a3cb8cf-dcb1-4441-8ab5-0009bf036b74',N'guid10',N'Loc10',0,0),
    (N'6a7a665d-0b4b-41a5-b01a-0009ee84e02b',N'guid10',N'Loc10',73,0),
    (N'b75a7e85-f929-4cc6-bf3f-000aaaab33e2',N'guid10',N'Loc10',0,0),
    (N'2341b029-55af-41a0-bfa3-000be8e71efe',N'guid10',N'Loc10',0,0),
    (N'0bf9396e-99fc-4bf0-9a48-000e90dc0cd2',N'guid10',N'Loc10',0,0),
    (N'948b91b3-5928-4eb8-ac1a-000f2d55be2a',N'guid10',N'Loc10',0,0),
    (N'50edd548-7a29-40cf-a082-000f5793b5b9',N'guid10',N'Loc10',0,0),
    (N'4ad8be92-ce5c-432e-a461-000ff002d0b5',N'guid10',N'Loc10',72,0),
    (N'265b0d5b-223b-4da1-9d4f-00107b652ae5',N'guid10',N'Loc10',0,0),
    (N'6670c15d-de83-43f4-a5fd-0010d56c574d',N'guid10',N'Loc10',0,0);

You need something to use as an order column. What would really help is full table definition and sample data instead of an image. [Here](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) is a great example. — Sean Lange, Jan 07 '19 at 21:48
@SeanLange Thank you for your response. I'll see what I can come with for table definition and sample data. — BeginnerOne, Jan 07 '19 at 22:14
@SeanLange I've included a table definition with some sample data. — BeginnerOne, Jan 08 '19 at 15:19
Your sample data either doesn't match the desired output or it makes no sense. For Loc1 you have 69 on and then another 32. Why is the 32 ignored? And you still have nothing in the data you can use to sort these rows. — Sean Lange, Jan 08 '19 at 15:23

score 0 · Answer 1 · answered Jan 08 '19 at 00:19

I don't have access to 2008 R2 but this solution should work from 2005+. It's mid page on the second link provided.

;with MyData as(
select 'loc1' [location], 69 PassengersOn, 0 PassengersOff
union all select 'loc2',61,0
union all select 'loc3',333,0
union all select 'loc4',57,21
union all select 'loc5',49,29
)


SELECT 
MyData.*
,RunningTotal.*

FROM MyData
cross apply (select 
                --SUM(PassengersOn)  PassOn
                --,SUM(PassengersOff) PassOff
                SUM(PassengersOn) - SUM(PassengersOff) as RunningTot

                from MyData MyDataApply
                where MyDataApply.[location]<= MyData.[location]
            ) as RunningTotal

ORDER BY MyData.[location]

score 0 · Accepted Answer · answered Jan 08 '19 at 00:52

0

I feel like if you were to index the table, you could do this pretty simply. I created a numbered Index via location. If you have the same route of these 10 stops and the data repeats loc1-loc10 over and over, you wouldn't want to use this. You need a primary key, or a date system to make this significantly easier.

SELECT  CONVERT(INT, RIGHT(Location, LEN(Location) - 3)) AS ID
    ,Location
    ,Passengerson
    ,PAssengersoff
INTO #IDtable
FROM #table

SELECT  ID 
    ,Location
    ,Passengerson
    ,Passengersoff
    ,(SELECT SUM(passengerson-passengersoff)
      FROM #IDtable b
      WHERE b.ID <= a.ID) AS Total
FROM #IDtable a
ORDER BY ID

this will give a result that looks like this:

Hopefully this helps you in some way.

answered Jan 08 '19 at 00:52

Nick A

126
7

The latter scenario ("the same route of these 10 stops and the data repeats loc1-loc10 over and over") is what I'm dealing with. Each location does in fact have it's own GUID. I'm supposing there is a way to use that GUID by self-joining to accomplish what I need? – BeginnerOne Jan 08 '19 at 14:46
Nick A, I tried something like this: `(SELECT SUM(stopEvent.PassOn- stopEvent.PassOff) FROM stopEvent se, StopEvent se2 WHERE se.IDLocation <= se2.IDLocation) AS RunningTotal` However I get a arithmetic overflow error. – BeginnerOne Jan 08 '19 at 15:22
The IDstopevent appears to be randomly generated. Do you perhaps have a date or time that goes along with these? Looking at that data, there is no way to identify which loc1 goes with its corresponding loc2 and loc3 and so on. Also there are different quantities of data for some of the locations. – Nick A Jan 08 '19 at 19:32
Nick A, yes, the IDstopevent is a globally unique identifier that is given to a new stop event. There is a ScheduledDepartureTime (datetime) field that each stop event has. The different quantities are due to the fact that I only took a sample from the table as there are over 10K items in it. – BeginnerOne Jan 09 '19 at 17:51
If I am understanding you correctly, you should be able to use the datetime column instead of the id column I created in basically the same way to create a running total. – Nick A Jan 10 '19 at 17:09

Subtract one column from another and get running total SQL Server 2008 R2

2 Answers2