sql expert needed for the query task

Job Description:

Project price [login to view URL]

1) Fix the control panel, only update rates

This is the link for access to the control panel, i made some words in english language

htps://[login to view URL]

When you add a new ofer you have to select the period from and to and add more price clicking on + blu link

under the price word

Here you can add price from 1 to 14 nights, for example you can add this ofer:

From 02/01/2023 to 31/01/2023

7 nights: 350 €

3 nights: 180 €

2 nights: 140 €

this mean all people want to stay in this hotel between this days will pay this prices

for example if a person want to stay 7 nights from 22 to 29/01/2023 will pay 350 € per person in double room

For this work i have 3 tables in database

1) oferte (where we add date and details of this ofer)

2) tarife (where we add this price (350, 180, 140) in this example

3) prezzi_per_note (where we add day by day the price for each night with the minimum stay)

In the control panel we have to fx the update ofer because if this ofer increese days, for example from

02/01/2023 to 08/02/2023 i can not insert this new days in the prezzi_per_note table, i'm able only to edit

the original date.

SEARCH RESULTS

your old work extract us the price from tarife table but we had many problems and sometmes not worked,

now is beter to make su sum using price per day bringing it from prezzi_per_note table

In this case we have price for each night and the price will be sure correct

In this table we add:

– id_prezzo (in english price_id, is autoincrement feld)

– id_tarifa (is the id of tarif in the table tarif)

– id_hotel (is the id of hotel who has this price)

– id_oferta (is id of ofer from table oferte, so we know all details of it)

– data_prezzo (is the single day)

– prezzo_note (is the price per day)

– prezzo_minimo_not (is the number of night for show this price)

– prezzo_atvo (if is 0 the price is actve, if is 1 the price is not actve)

Example of search result per day with single price for each day

2023-01-20: 50 €

2023-01-21: 60 €

2023-01-22:70 €

so if someone will search from 20-22 jannuary the price per person is: 50+60+70= 180 €

All the price are based on double room, so in this case, if user select 2 adults we have to show 360 €

In the form search is possible to search for only 1 person, in this case if in the table oferte the

supplemento_singola or supplemento_doppia_uso_singola have a value we have to add this value to the

price, for example if the supplemento_singola is 10 for this period we have 180 € + 10+10+10: total price for

single room 210 €

The same is if user select 3rd and 4th bed, we will add discount for this based on the age and when user will

search in the form for 2 adults + 1 children 5 years old we will show 180+180+discount for this range age

The search result page is [login to view URL]

Beceriler: SQL, Microsoft SQL Server, MySQL, PHP, ASP

Proje NO: #35388571