Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Passengers # at every bus stop Puzzle ]

Puzzle Statement

  1. The Puzzle is simple.
  2. You have given a table where you have got information about different stops of a Bus and the passengers board the bus and passgengers alight from the bus.
  3. Please check out the sample input and expected output for details.

ThePassengersPuzzle

Sample Input

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

Expected Output

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

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


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 



Update May 9 | Solutions — Pawan Kumar Khowal


--

/************   SOLUTION 1         ****************/

;WITH CTE AS 
(
      select *, nxt = ONS - offs - CASE WHEN Spot_Check IS NULL THEN 0 ELSE 1 END
      from Busload
)
SELECT *, (Select sum(nxt) FROM CTE A WHERE A.StopNumber <= t.StopNumber ) PassengerAtEveryStop
FROM CTE t


/************   SOLUTION 2        ****************/

;WITH CTE AS 
(
      select *, nxt = ONS - offs - CASE WHEN Spot_Check IS NULL THEN 0 ELSE 1 END
      from Busload
)
SELECT * , SUM(nxt)  OVER (ORDER BY stopNUmber) PassengerAtEveryStop
FROM CTE t


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com

Advertisements