Bus Stops

The Problem

I have a table that holds records for a stops along a bus route. Each
records has a number of people getting on, number of people getting
off, a spot check, and a current load column.
The spot check column is the to verify that the sensors on the bus are
working correctly; the most reliable number to be use in the is
SPOT_CHECK.


Table Structure as follows:


ID (identity column)
ROUTE (Description of the bus route)
ONS (# of people getting on)
OFFS (# of people getting off)
SPOT_CHECK (visual spot count of people on the bus)
LOAD (Calculated load on the bus)

Solution 1 (me):

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)
go
SELECT b.ROUTE, b.StopNumber, b.ONS, b.OFFS, b.SPOT_CHECK,
SUM(COALESCE(b1.SPOT_CHECK, 0) +
CASE WHEN b1.SPOT_CHECK IS NULL THEN b1.ONS ELSE 0 END - CASE WHEN
b1.SPOT_CHECK IS NULL THEN b1.OFFS ELSE 0 END)
FROM BusLoad b JOIN BusLoad b1 ON b1.StopNumber <= b.StopNumber AND
b1.ROUTE = b.ROUTE
WHERE NOT EXISTS(SELECT 1 FROM BusLoad b2 WHERE b2.ROUTE = b.ROUTE
AND b2.StopNumber <= b.StopNumber AND b1.StopNumber < b2.StopNumber
AND b2.SPOT_CHECK IS NOT NULL)
GROUP BY b.ROUTE, b.StopNumber, b.ONS, b.OFFS, b.SPOT_CHECK
go
DROP TABLE BusLoad


Solution 2 (Jim Underwood):

select a.StopNumber
, a.ROUTE
, a.ONS
, a.OFFS
, a.SPOT_CHECK
, sum(case when c.spot_check is null
then c.ons-c.offs else c.spot_check end)
from BusLoad as a
left outer join BusLoad as b
on b.route = a.route
and b.StopNumber =
(
select max(b1.StopNumber)
from BusLoad b1
where b1.route = b.route
and b1.StopNumber <= a.StopNumber
and b1.spot_check is not null
)
inner join BusLoad as c
on c.route = a.route
and c.StopNumber between (case when b.StopNumber is null
then 0 else b.StopNumber end) and a.StopNumber
group by a.StopNumber
, a.ROUTE
, a.ONS
, a.OFFS
, a.SPOT_CHECK;