1
I have two tables with one-to-many relationship.
(1) Room_Type &
(2) RateSheet
Room_Type Table (Example)
+-------------+-----------+
|Property_Name| Room_Type |
+-------------+-----------+
|Property 1 | R_Type 1 |
|Property 2 | R_Type 2 |
|Property 3 | R_Type 3 |
+-------------+-----------+
RateSheet Table (Example)
+----------+------------+------------+------+
|Room_Type | Start_Date | End_Date | Rate |
+----------+------------+------------+------+
|R_Type 1 | 01-03-2014 | 10-03-2014 | 100 |
|R_Type 1 | 11-03-2014 | 20-03-2014 | 120 |
|R_Type 1 | 21-03-2014 | 10-03-2014 | 130 |
+----------+------------+------------+------+
For each room type there is one or more rates & rates differ at different periods.
I hava a search form which will display a query result of Property Name, Room Type, Total for specific number of days.
In the form I have to enter two dates, Start Date & End Date
For example:
Start Date = 05-03-2014
End Date = 12-03-2014
Now the query should display the result similar to the table below
+---------------+-----------+-------+
| Property_Name | Room_Type | Total |
+---------------+-----------+-------+
| Property 1 | R_Type 1 | 720 |
+---------------+-----------+-------+
How total is calculated?
From 1st to 10th March, the rate is 100 per day.
From 11th to 20th March, the rate is 120 per day.
In the form I've entered 05-03-2014 as start date & 12-03-2014 as end date. So, rate is calculated for (5,6,7,8,9,10 & 11) of March (12th March not included when calculating the rate)
So, 100 is the rate for (5,6,7,8,9 & 10th March) and 120 for 11th March.
Total = (6 x 100) + (1 x 120) = 720
I want to make a query to generate this result.
Please help guys... :)
