Project Detail

Description

Hi ,

I need some help filtering results on mysql I have a table with :

Hotel { Name of the hotel }
start_date { Date When season starts }
end_date { Date When season ends }
room { Room that the hotel has }
Price { Price of the room for the hotel }
contract_id { Contract reference number for hotel }
Rate_base { Prices per Person = PP / Prices for Unit = PU }

The values that i output from the table is as follows or please check attach mysql table.

Room for 2 People
Hotel: Bayside | ROOM: Studio | PRICE: 20 | RATE BASE: PU
Hotel: Bayside | ROOM: One bedroom apartment | PRICE: 40 | RATE BASE: PU
Hotel: Sheraton algarve | ROOM: Twin | PRICE: 15 | RATE BASE: PP
Hotel: Sheraton algarve | ROOM: Suite | PRICE: 25 | RATE BASE: PP
Hotel: Savoy Hotel | ROOM: Twin Room | PRICE: 9 | RATE BASE: PP
Hotel: Savoy Hotel | ROOM: Junior Suite | PRICE: 16 | RATE BASE: PP
It should have a filter to identify when rate base is PP it multiplies by the number of people wich in this case is 2 so the price is {price} * 2 when rate_base is PP when rate_base = PU there’s no need to multiplie by number of pax; can anyone sort this out?

The correct output results ordered by price for each unit shoud be the ones mentioned below, please results should output what units are the cheapest and not wich record is cheaper. I will give example how it’s done when rate_base is = PU and PP

Room for 2 People
Hotel: Savoy Hotel | ROOM: Twin Room | PRICE: 9 | RATE BASE: PP |
ex: 9*2 = 18

Hotel: Savoy Hotel | ROOM: Junior Suite | PRICE: 16 | RATE BASE: PP
ex: 16* 2 = 32

Hotel: Bayside | ROOM: Studio | PRICE: 20 | RATE BASE: PU
ex: 20
Hotel: Bayside | ROOM: One bedroom apartment | PRICE: 40 | RATE BASE: PU
Ex: 40
Hotel: Sheraton algarve | ROOM: Twin | PRICE: 15 | RATE BASE: PP
Ex: 15*2 = 30

Hotel: Sheraton algarve | ROOM: Suite | PRICE: 25 | RATE BASE: PP
Ex: 25*2 = 50



Additional files submitted:
zrates.sql