Selecting from a date range

[luke64]

Apolgies if this has already been answered...

Right, I have a season table where I specify each season (Winter, Summer, Spring etc.) with a 'date from' and 'date to' field which is formatted as dd/mm (no year).

I also have a property table that defines a properties grade, I also have a table used to define each grade ('Grade', 'Description')

Then finally I have a relationship table which covers 'Grade', 'Season', and 'Price'.

I need a query that can look at the 'Date_From' in my Booking table, select the correct season, and look at the properties grade to select the right price for the correct season and grade...

I'm compleaty stumped on this one, and any help would be much apprecieated!

thanks,
Luke

[willakawill]

Hi. Would you submit the schema for this database. The table names and column names and data types. thanks

[luke64]

tblProperty
Property_Name = text
Property_ID = autonumber
Grade = text
Address = text

tblBooking
Customer_ID = integer
Property_ID = integer
Booking_ID = autonumber
Date_From = short date
Date_To = short date

tblGrade
Grade = text
Description = text

tblRentalPrice
Grade = text
Season = text
Price = currency

tblSeason
Season = text
Date_From = date (formatted as "dd/mm")
Date_To = date (formatted as "dd/mm")

[willakawill]

tblProperty
Property_Name = text
Property_ID = autonumber
Grade = text
Address = text

tblBooking
Customer_ID = integer
Property_ID = integer
Booking_ID = autonumber
Date_From = short date
Date_To = short date

tblGrade
Grade = text
Description = text

tblRentalPrice
Grade = text
Season = text
Price = currency

tblSeason
Season = text
Date_From = date (formatted as "dd/mm")
Date_To = date (formatted as "dd/mm")

I guess your problem is in bringing together the season stuff to match up with the booking date. Try this out. In your sql statement format both as julian dates. i.e. the day of the year from 1 to 365.

FORMAT([tblSeason].[Date_From], "y") AS DateFromDay

You will find it easier to compare dates this way.

[luke64]

Thanks for replying, what I'm having most trouble is actually writing the query to to do this, I'm very new to SQL, and before only created queries via the design view.

I was thinking along the lines of using the SQL BETWEEN function, but I'm unsure how to specifiy each date range within my seasons table to allow the query to return the season.

My VB skills tell me I should just code a solution, but I'm learning SQL, not VB!!

[willakawill]

Thanks for replying, what I'm having most trouble is actually writing the query to to do this, I'm very new to SQL, and before only created queries via the design view.

I was thinking along the lines of using the SQL BETWEEN function, but I'm unsure how to specifiy each date range within my seasons table to allow the query to return the season.

My VB skills tell me I should just code a solution, but I'm learning SQL, not VB!!

I understand this difficulty. My previous reply was in fact one solution but you will have to take it a step or 2 further to make it work.
Because your date formats are different, if you convert them to julian date format you can use BETWEEN quite easily. As an example you might have spring listed as from april 5th to june 8th. 95th day of the year to 159th day of the year. You need to check what season april 12th 2007 falls in. Well that is 102nd day of the year so is 102 between 95 and 159? Easy.

[NeoPa]

Apolgies if this has already been answered...

Right, I have a season table where I specify each season (Winter, Summer, Spring etc.) with a 'date from' and 'date to' field which is formatted as dd/mm (no year).

I also have a property table that defines a properties grade, I also have a table used to define each grade ('Grade', 'Description')

Then finally I have a relationship table which covers 'Grade', 'Season', and 'Price'.

I need a query that can look at the 'Date_From' in my Booking table, select the correct season, and look at the properties grade to select the right price for the correct season and grade...

I'm compleaty stumped on this one, and any help would be much apprecieated!

thanks,
Luke

On a strictly SQL basis, and comparing the Date_From field of tblBooking, check this out and see if it illustrates the concept for you :

SELECT tblBooking.*,
       tblSeason.Text
FROM tblBooking, tblSeason
WHERE tblBooking.From_Date Between
      tblSeason.Date_From And tblSeason.Date_To

It uses an Outer Join (no join) but drops any results where the season is not matched.

[willakawill]

On a strictly SQL basis, and comparing the Date_From field of tblBooking, check this out and see if it illustrates the concept for you :

SELECT tblBooking.*,
       tblSeason.Text
FROM tblBooking, tblSeason
WHERE tblBooking.From_Date Between
      tblSeason.Date_From And tblSeason.Date_To

It uses an Outer Join (no join) but drops any results where the season is not matched.

I don't think this will work becuase the season dates do not have a year, and if they did it could be any year so it would not match with the booking from date

[NeoPa]

You're absolutely right of course Will. I'm sure this was discussed earlier too so I had no excuse for that oversight. Let's see if I can make up for that lapse in concentration. Extra complications as one season probably wraps around the end of the year :( Not kidding, this complicates matters more than I'd expected.
This means of course, that the data is not stored correctly. It doesn't make much sense to store MM/DD data in a Date/Time field as that must contain a year part. It's a possibility to store it that way, but all code would have to know to interpret it without the year part (Simply formatting it that way will not suffice). Julian date format, though a very clever idea, will also not work perfectly, as February will confuse things for dates that follow in leap years (It'll be a fairly good approximation mind).

SELECT B.*,
       S.Text
FROM tblBooking AS B, tblSeason AS S
WHERE (((S.Date_To>S.Date_From)
  AND (Format(B.From_Date,'mmdd') Between
      Format(S.Date_From,'mmdd') And Format(S.Date_To,'mmdd')))
   OR ((S.Date_To<S.Date_From)
  AND (Format(B.From_Date,'mmdd') Not Between
      Format(S.Date_From,'mmdd') And Format(S.Date_To,'mmdd'))))

For this to work as is, all the dates set up in tblSeasons must share the same year (Even though it's never displayed or even used explicitly).