CODEWITHSUNDEEP
sqlteradata
dev
dev

Reputation: 961

Access the previous row in select

I have a scenario as below 

--source data
departuredttm        flight_source flight_destination available_seats   
13-07-2016 04:00:00     A               B               200         
13-07-2016 08:00:00     A               B               320             
13-07-2016 08:20:00     A               B               20

I have a lookup table which tell how many total passengers are there for this source and destinatin whose flights are delayed and needs to adjusted in available seats in source data.lookup table is like this. 

--lookup table for passenger_from_delayed_flights
flight_source  flight_destination  passengers
A              B                   500

now I have to adjust these 500 passengers in available seats as in source data

---output
DepartureDttm        flight_source  flight_destination AVAILABLE_SEATS      PASSENGERS_TO_ADJUST            PASSENGER_LEFT
13-07-2016 04:00:00  A              B                  200                  500                             300
13-07-2016 08:00:00  A              B                  320                  300                             20
13-07-2016 08:20:00  A              B                  20                   20                              0

initially passenger to adjust is 500 where we have 200 seats , next 320 seats are available and we have to adjust 300 (500-200) passengers.

Please help

Thanks

Upvotes: 1

Views: 69

Answers (1)

dnoeth
dnoeth

Reputation: 60462

Your expected result is probably wrong, the 2nd flight already has enough seats, so PASSENGER_LEFT should be -20 (or 0).

This is a calculation based on a running total:

passengers - SUM(available_seats)
             OVER (ORDER BY departuredttm 
                   ROWS UNBOUNDED PRECEDING) AS PASSENGER_LEFT

available_seats + PASSENGER_LEFT AS PASSENGERS_TO_ADJUST

Upvotes: 1

Related Questions