0
CREATE PROCEDURE available_rooms(
hotelname_param VARCHAR(45),
date_param  DATE)
BEGIN

IF date_param IS NULL  THEN -- if the booking date is null
    SIGNAL SQLSTATE '22003' -- signalling error
    SET MESSAGE_TEXT = 'the booing date should not be null',
    MYSQL_ERRNO = 1264;
END IF;

SELECT h.Hotel_id AS HotelID,hotelname_param AS HotelName,((h.Floor_count) * (h.Roomsperfloor)) AS TotalRooms,
    g.Guest_firstname AS Guestname,b.Room_count AS RoomsBooked,
    (((h.Floor_count) * (h.Roomsperfloor)) - b.Room_count) AS AvailableRooms 
FROM hotel h
JOIN guests g ON h.Hotel_id = g.Hotel_id
JOIN booking b ON g.Guest_id = b.Guest_id
WHERE b.BookingDate = date_param;
END

CALL available_rooms('Grand Central Inn', '2018-01-11')

when I call the procedure its displaying output as THIS So the available rooms of first row should be used for the next row calculation, but I am not able to get that.Can anyone help me out

Achyuth Bhattar
  • 11
  • 1
  • Provide the complete stored procedure code not some part or some parts from it. – Raymond Nijland Apr 06 '18 at 17:55
  • Search for how to get `row number in mysql`. Once you have row numbers, you can join the table with itself using `t1.row_number = t2.row_number - 1` for your calculations. – clinomaniac Apr 06 '18 at 17:56
  • 1
    No @clinomaniac he needs to search how to simulate LAG in MySQL. Search on Stackoverflow "MySQL LAG" -> https://stackoverflow.com/search?q=MySQL+LAG. – Raymond Nijland Apr 06 '18 at 17:58
  • Another useful link on Analytical functions in MySQL: https://explainextended.com/2009/03/10/analytic-functions-first_value-last_value-lead-lag/ – Jorge Campos Apr 06 '18 at 18:05
  • 1
    Possible duplicate of [Simulate lag function in MySQL](https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql) – Jorge Campos Apr 06 '18 at 18:06

0 Answers0