how to calculate a bill from several tables on mysql?
- by Audel
I'm using mysql to create an hotel booking system, but i am struggling a little bit to calculate the final bill.
I need a SELECT command to get data from several tables and make some calculations.
Basically I just need to get the 'night cost' from a table called 'room_types'. Then, use DATEDIFF function to get the difference of days between the 'checkin' and 'checkout' columns in the table 'room_booking' and multiply the difference with the night cost and display the total.
These are the tables I would be using: are room_booking, room_types, booking, and room.
One booking may have several room bookings, so Im looking for a table that looks something like this:
+------------+------------+---------------+------------------+
| bookingid | Room price | nights stayed | total room price |
+------------+------------+---------------+------------------+
| B001 | 30.00 | 4 | 120.00 |
+------------+------------+---------------+------------------+
| B001 | 40.00 | 3 | 120.00 |
+------------+------------+---------------+------------------+
booking id comes from table 'booking' room price from 'room_types', 'nights stayed' is calculated from the table room_booking, using the datediff command between checkin and checkout .
I hope i was clear
