SQL Query for Microsoft Access database

I need a SQL query writing for an Microsoft Access 2003 Database.

There are two tables, 'Properties' and 'PropertyRates':

Properties

PropertyId (Integer) PropertyName (Memo)

-both of these fields will be populated in every record

PropertyRate

PropertyRateId (Integer) PropertyId (Integer) FromDate (Double – date is stored as a number) ToDate (Double – date is stored as a number) NightlyRate (Double) WeeklyRate (Double) MidweekRate (Double) WeekendRate (Double) MinimumNightsStay (Integer) Active (Boolean – 0=inactive, 1=active)

The PropertyRateId, PropertyId, FromDate, ToDate and Active fields will be populated in every record. Each record will have one or more of these fields populated: NightlyRate, WeeklyRate, MidweekRate, WeekendRate – unpopulated fields will have null value. If the MidweekRate is populated with a value other than null then the WeekendRate will also have a non-null value and vice-versa. The MinimumNightsStay field will be populated with an integer greater that 0 or will be null.

These two tables can be joined on the PropertyId fields.

The following variables will be fed into the query:

iFromDate (Double – a date represented by a number) iToDate (Double – a date represented by a number) iNumberOfNights (Integer – the number of nights in the 'iFromDate' to 'iToDate' date range) iMidweekNights (Integer – the number of midweek nights in the 'iFromDate' to 'iToDate' date range) iWeekendNights (Integer – the number of weekend nights in the 'iFromDate' to 'iToDate' date range)

The query needs to return one row per property for each property that has at least one active 'PropertyRate' that overlaps the date range defined by 'iFromDate' - 'iToDate' and that has a 'MinimumNightsStay' greater or equal to the 'iNumberOfNights' variable. 'PropertyRate' records that have a 'MinimumNightsStay' with a null value must be included. The row needs to contain the following values:

PropertyId (Integer) – The id the property from the 'PropertyId' field. PropertyName (String) – The name of the property from the 'PropertyName' field. iMinRate (Double) – The lowest matching rate for the date range specified iMaxRate (Double) – The highest matching rate for the date range specified

These values need to be calculated in the query following these rules:

If the 'iNumberOfNights' variable is 7 or higher (or if the 'NightlyRate', 'MidweekRate' and 'WeekendRate' fields are all null) and the 'WeeklyRate' field is not null, then calculate the rate based upon this field and the 'iNumberOfNights' variable (eg. Rate=(WeeklyRate/7)*iNumberOfNights).

NOTE: If the 'iNumberOfNights' variable is 7 or higher and 'WeeklyRate' field IS null then use the following rules to calculate the rate.

If the 'iNumberOfNights' variable is less than 7 and the 'MidweekRate' and 'WeekendRate' fields are not null, then calculate the rate based upon these fields and the 'iMidweekNights' and 'iWeekendNights' variables (eg. Rate=(MidweekRate*iMidweekNights)+(WeekendRate*iWeekendNights)).

If the 'iNumberOfNights' variable is less than 7 and the 'MidweekRate' and 'WeekendRate' fields ARE null, then calculate the rate based upon the 'NightlyRate' field and the 'iNumberOfNights' variable (eg. Rate=NightlyRate*iNumberOfNights).  

The results should be ordered by 'iMinRate' (Ascending)