By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

Selecting from a date range

P: 3
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
Feb 26 '07 #1
Share this Question
Share on Google+
8 Replies


100+
P: 1,646
Hi. Would you submit the schema for this database. The table names and column names and data types. thanks
Feb 26 '07 #2

P: 3
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")
Feb 27 '07 #3

100+
P: 1,646
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.
Expand|Select|Wrap|Line Numbers
  1. FORMAT([tblSeason].[Date_From], "y") AS DateFromDay
You will find it easier to compare dates this way.
Feb 27 '07 #4

P: 3
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!!
Mar 1 '07 #5

100+
P: 1,646
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.
Mar 2 '07 #6

NeoPa
Expert Mod 15k+
P: 31,487
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT tblBooking.*,
  2.        tblSeason.Text
  3. FROM tblBooking, tblSeason
  4. WHERE tblBooking.From_Date Between
  5.       tblSeason.Date_From And tblSeason.Date_To
It uses an Outer Join (no join) but drops any results where the season is not matched.
Mar 2 '07 #7

100+
P: 1,646
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT tblBooking.*,
  2.        tblSeason.Text
  3. FROM tblBooking, tblSeason
  4. WHERE tblBooking.From_Date Between
  5.       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
Mar 2 '07 #8

NeoPa
Expert Mod 15k+
P: 31,487
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).
Expand|Select|Wrap|Line Numbers
  1. SELECT B.*,
  2.        S.Text
  3. FROM tblBooking AS B, tblSeason AS S
  4. WHERE (((S.Date_To>S.Date_From)
  5.   AND (Format(B.From_Date,'mmdd') Between
  6.       Format(S.Date_From,'mmdd') And Format(S.Date_To,'mmdd')))
  7.    OR ((S.Date_To<S.Date_From)
  8.   AND (Format(B.From_Date,'mmdd') Not Between
  9.       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).
Mar 2 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.