Help With a DATETIME Query

[Shaun]

Hi,

I have a table called Bookings which has two important columns;
Booking_Start_D ate and Booking_End_Dat e. These columns are both of type
DATETIME. The following query calculates 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). However, when a booking spans more than one
day the query doesn't work, for example if a user has a booking that starts
on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5
hours for both days. Any help here would be greatly appreciated.

SELECT 8.5 - (SUM(((DATE_FOR MAT(B.Booking_E nd_Date, '%k') * 60 ) +
DATE_FORMAT(B.B ooking_End_Date , '%i')) - ((DATE_FORMAT(B .Booking_Start_ Date,
'%k') * 60 ) + DATE_FORMAT(B.B ooking_Start_Da te, '%i'))) / 60) AS
Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND
B.Booking_Statu s <> '1' AND NOT ( '2003-10-07' <
DATE_FORMAT(Boo king_Start_Date , "%Y-%m-%d") OR '2003-10-07' >
DATE_FORMAT(Boo king_End_Date, "%Y-%m-%d") )

Thanks for your help

[David Portas]

Re: Help With a DATETIME Query

You can do this using a Calendar table:

CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY)

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'2010 1231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATED IFF(D,'19991231 ',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

And a Numbers table: http://tinyurl.com/pta3

Here's a query which will work for any specified range of dates in the
Calendar table:

SELECT C1.caldate, 8.5 - COALESCE(A.Book ed_Hours,0) AS Available_Hours
FROM Calendar AS C1
LEFT JOIN
(SELECT C2.caldate,
CAST(COUNT(DIST INCT DATEADD(MINUTE, N.num,C2.caldat e))
/60.0 AS DECIMAL(4,2)) AS Booked_Hours
FROM Calendar AS C2
JOIN Numbers AS N
ON N.num BETWEEN 540 AND 1049
JOIN WMS_Bookings AS W
ON DATEADD(MINUTE, N.num,C2.caldat e) >= W.Booking_Start _Date
AND DATEADD(MINUTE, N.num,C2.caldat e) < W.Booking_End_D ate
AND
((W.Booking_Sta rt_Date>=C2.cal date
AND W.Booking_Start _Date < DATEADD(DAY,1,C 2.caldate))
OR
(W.Booking_End_ Date>=C2.caldat e
AND W.Booking_End_D ate < DATEADD(DAY,1,C 2.caldate)))
GROUP BY C2.caldate) AS A
ON C1.caldate = A.caldate
WHERE C1.caldate BETWEEN '20030101' AND '20030131'

Note the redundant predicates in the derived table's WHERE clause. They help
improve the join performance. If overlapping bookings do not occur in your
system then you can remove DISTINCT from the query to improve performance
further.

--
David Portas
------------
Please reply only to the newsgroup
--