question

write a procedure that Calculate and display total income from all sources of all hotels. Totals must be printed by month, and for each month by event and service type. Include discounts.( 10% discount if the reservation date is 2 month before reservation start date).
The tables are below

Hotel Table have

Hotel_id, hotel_name, Hotel_city, Hotel_state, Hotel_zip,Hotel_phone

Reservation Table have

Reservation_id, Hotel_id, Room_num, Service_id, Guest_name, Reservation_date, Reservation_start_date, Reservation_end_date, cancelation_date, Num_of_guest, event_type

Room Table have

Room_num, Hotel_id, Room_type, Room_capacity, Room_cost

service table have

service_id, Service_type, Service_cost

this is what i tried, but i want to write it in a procedure form, how do i do that. please help.Thanks

select month (Reservation_end_date) as , event_type,
sum(case when days>= 2 then cost- (cost/100)* 10
else cost) as total_cost)



((select distinct reservation.hotel_id,reservation_date, reservation_start_date,
reservation_end_date, event_type, room.room_type as R_type ,room_cost as R_cost,
months_between(reservation_start_date,reservation_date)as months
from reservation, room
where reservation.hotel_id = room.hotel_id;)

union

 (select hotel_name, reservation_date, reservation_start_date,
reservation_end_date, event_type, services_type, services_cost as cost,
months_between(reservation_start_date,reservation_date)as month
from reservation,service, hotel
where reservation.services_id = service.services_id 
and reservation.hotel_id = hotel.hotel_id;))

group by month(reservation_end_date),event_type;