Shaun Thornburgh wrote:
Hi,
I have a table called Bookings which has two important columns;
Booking_Start_T ime and Booking_End_Tim e. These columns are both of type
DATETIME. Given any day how can I calculate how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they have unbooked on a particular day (i.e. 8.5 hours less the time
of any bookings on that day), can this be done with a query
or do I have to work it out in my code?
Thanks for your help
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
It can be done in a query. I assume there are other columns in the
Bookings table that define what type of booking each record contains.
But, I'll go w/ the specs you gave. Hopefully the DateTime columns
have both the date and the time; otherwise, this could get ugly.
PARAMETERS [Date?] DateTime;
SELECT 8.5 - Sum(DateDiff("h ", Booking_Start_T ime, Booking_End_Tim e))
As Available
FROM Bookings
WHERE Booking_Start_T ime Between [Date?] And DateAdd("n", -1,
[Date?]+1)
What it does:
8.5 - Sum(DateDiff("h ", Booking_Start_T ime, Booking_End_Tim e)) - since
there are 8.5 hours between 9:00 and 17:30, just subtract the total
booked hours from 8.5 to get the available hours.
If you have another column like Room_Number (something that identifies
what is stopping and starting on the DateTime values) you'd include it
in the SELECT clause and create a GROUP BY clause like this:
GROUP BY Room_Number
The WHERE clause uses the date entered by the user when the query
runs. It selects dates between the beginning of the Date (00:00:01)
and the end of the Date ((Date + 1 Day) - 1 minute , which equals
23:59 of the Date).
HTH,
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP3nQboechKq OuFEgEQIHZwCgtD drn3WH57Fpa6zx5 Qkw12mGz68An3SY
3mapIDQYaN7KJZ2 UHKmzp8En
=bWlP
-----END PGP SIGNATURE-----