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;
Answer by KenJ ·
CREATE OR REPLACE your_procedure_name AS
... your_query ...
More info on the Oracle site - https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm
Write a pl/sql procedure for the following question. Show specific event: Given an event type (Birthday, Wedding, etc.) display all events of that type in all hotels along with the address of the hotel and the date of the event.