Bus seat calculation dynamic algorithm with variation in stops

Question

Given the database result below,

• A null value in BusStopNumber denotes to Origin which in Mogadishu in this case
• A null value in BusStopNumber2 denotes to Destination which in Nairobi in this case

If are a total of 10 seats on a bus.

What is the best way to calculate the number of seats left when a passenger wants to travel from Mogadishu to Hargeisa (Which is the final stop before the destination) and we have 2 Seats booked from Initial point to final Point? Similarly, I want to calculate seats available from the SeatsCount column which denotes how many seats have been booked for all routes.

Answer 1

declare @booking table
(
    id int identity,
    busid int,
    scheduleid int,
    stopnumber smallint,
    stopnumber2 smallint,
    seatscount smallint
);

insert into @booking(busid, scheduleid, stopnumber, stopnumber2, seatscount)
values 
(1, 6, null, null, 2),
(1, 6, null, 1, 2),
(1, 6, null, 2, 1),
(1, 6, null, 3, 2),
(1, 6, 1, null, 2),
(1, 6, 1, 2, 1),
(1, 6, 1, 3, 1),
(1, 6, 2, null, 1),
(1, 6, 2, 3, 1),
(1, 6, 3, null, 2);

select * from @booking;


select *, 
--total number of passengers that got on the bus at previous&current stops
(select isnull(sum(e.seatscount), 0) from @booking as e where e.busid = b.busid and e.scheduleid = b.scheduleid and isnull(e.stopnumber, 0) <= isnull(b.stopnumber, 0))
-
--total number of passengers that got off the bus at previous&current stops
(select isnull(sum(d.seatscount), 0) from @booking as d where d.busid = b.busid and d.scheduleid = b.scheduleid and d.stopnumber2 <= b.stopnumber)
as PassengersOnBusAtStopNumber
from @booking as b;