Calculate loads and avoiding cursors

Question

Given the following table structure, which is a representation of a bus route where passengers get on and off the bus with a door sensor. And, there is a person who sits on that bus with a clipboard holding a spot count.

CREATE TABLE BusLoad(
ROUTE CHAR(4) NOT NULL,
StopNumber INT NOT NULL,
ONS INT,
OFFS INT,
SPOT_CHECK INT)
go
INSERT BusLoad VALUES('AAAA', 1,   5,   0,    null)
INSERT BusLoad VALUES('AAAA', 2,   0,   0,    null)
INSERT BusLoad VALUES('AAAA', 3,   2,   1,    null)
INSERT BusLoad VALUES('AAAA', 4,   6,   3,    8)
INSERT BusLoad VALUES('AAAA', 5,   1,   0,    null)
INSERT BusLoad VALUES('AAAA', 6,   0,   1,    7)
INSERT BusLoad VALUES('AAAA', 7,   0,   3,    null)

I want to add a column "LOAD" to this table that calculates the load at each stop.

Load = Previous stops load + current stop ONS - Current stop's OFFS if SPOT_CHECK is null, otherwise LOAD = SPOT_CHECK

Expected Results:

ROUTE   StopNumber  ONS OFFS    SPOT_CHECK  LOAD
AAAA    1           5   0       NULL        5
AAAA    2           0   0       NULL        5
AAAA    3           2   1       NULL        6
AAAA    4           6   3       8           8
AAAA    5           1   0       NULL        9
AAAA    6           0   1       7           7
AAAA    7           0   3       NULL        4

I can do this with a cursor, but is there a way to do it using a query?

Answer 1

You can use the following query:

select ROUTE, StopNumber, ONS, OFFS, SPOT_CHECK,
       COALESCE(SPOT_CHECK, ONS - OFFS) AS ld,
       SUM(CASE WHEN SPOT_CHECK IS NULL THEN 0 ELSE 1 END) 
       OVER (PARTITION BY ROUTE ORDER BY StopNumber) AS grp
from BusLoad

to get:

ROUTE   StopNumber  ONS OFFS    SPOT_CHECK  ld  grp
----------------------------------------------------
AAAA    1           5   0       NULL        5   0
AAAA    2           0   0       NULL        0   0
AAAA    3           2   1       NULL        1   0
AAAA    4           6   3       8           8   1
AAAA    5           1   0       NULL        1   1
AAAA    6           0   1       7           7   2
AAAA    7           0   3       NULL       -3   2

All you want now is the running total of ld over ROUTE, grp partitions of data:

;WITH CTE AS (
 ....
 previous query here
)
select ROUTE, StopNumber, ONS, OFFS, SPOT_CHECK, grp,
       sum(ld) over (PARTITION BY ROUTE, grp ORDER BY StopNumber) as load
from cte

Demo here

Note: The above query works for versions starting from 2012. If you want a query for 2008 you have to somehow simulate sum() over (order by ...). You can find many relevant posts here in SO.

Answer 2

You may use recursive query

with act_load as
(
  select *, ons load
  from busload
  where stopnumber = 1 and route = 'AAAA'
  union all
  select b.*, case when b.spot_check is null then l.load + b.ons - b.offs
              else b.spot_check
              end load
  from busload b
  join act_load l on b.StopNumber = l.StopNumber + 1 and
                     b.route = l.route
)
select *
from act_load

dbfiddle demo