Calculating time difference

[Shaun Thornburgh]

Hi,

I have a table called Bookings which has two important columns;
Booking_Start_Time and Booking_End_Time. 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

[MGFoster]

Shaun Thornburgh wrote:

Hi,

I have a table called Bookings which has two important columns;
Booking_Start_Time and Booking_End_Time. 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_Time, Booking_End_Time))
As Available
FROM Bookings
WHERE Booking_Start_Time Between [Date?] And DateAdd("n", -1,
[Date?]+1)

What it does:

8.5 - Sum(DateDiff("h", Booking_Start_Time, Booking_End_Time)) - 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/AwUBP3nQboechKqOuFEgEQIHZwCgtDdrn3WH57Fpa6zx5Qkw12 mGz68An3SY
3mapIDQYaN7KJZ2UHKmzp8En
=bWlP
-----END PGP SIGNATURE-----