Calculating Time Difference

[Shaun]

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

[Shervin Shapourian]

Shaun,

Take a look at DateDiff function in books online. Something like this will
help you. This query return the available time in minutes.

select Date,
DateDiff(minute, '09:00','17:30') - Sum(BookTime)
from (select convert(char(10), Booking_Start_Time, 112) as Date,
DateDiff(minute, Booking_Start_Time, Booking_End_Time) as
BookTime
from YourTable
) as Tmp
group by Date

Shervin

"Shaun" <sh***@mania.plus.com> wrote in message
news:bl**********@reader-00.news.insnet.cw.net...

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

[John Gilson]

"Shaun" <sh***@mania.plus.com> wrote in message news:bl**********@reader-00.news.insnet.cw.net...

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

Here's a solution that allows bookings to overlap, multiple interested
non-overlapping intervals to be specified (you had specified one, 0900
to 1730) and both bookings and interested intervals to span more than
one day.

-- Bookings can overlap
CREATE TABLE Bookings
(
booking_start_time DATETIME NOT NULL,
booking_end_time DATETIME NOT NULL,
CHECK (booking_end_time >= booking_start_time),
PRIMARY KEY (booking_start_time, booking_end_time)
)

-- Sample data
INSERT INTO Bookings (booking_start_time, booking_end_time)
VALUES ('20030930 8:30AM', '20030930 10:30AM')
INSERT INTO Bookings (booking_start_time, booking_end_time)
VALUES ('20030930 10:00 AM', '20030930 12:30PM')
INSERT INTO Bookings (booking_start_time, booking_end_time)
VALUES ('20030930 2:00PM', '20030930 4:30PM')
INSERT INTO Bookings (booking_start_time, booking_end_time)
VALUES ('20030930 9:00PM', '20030930 11:30PM')

-- Interested intervals can't overlap
CREATE TABLE InterestedIntervals
(
interval_start DATETIME NOT NULL,
interval_end DATETIME NOT NULL,
CHECK (interval_end >= interval_start),
PRIMARY KEY (interval_start, interval_end)
)

-- Sample data
-- Note that multiple interested intervals can be specified
INSERT INTO InterestedIntervals (interval_start, interval_end)
VALUES ('20030930 8:00AM', '20030930 1:00PM')
INSERT INTO InterestedIntervals (interval_start, interval_end)
VALUES ('20030930 2:00PM', '20030930 6:00PM')
INSERT INTO InterestedIntervals (interval_start, interval_end)
VALUES ('20030930 8:00PM', '20030930 11:59PM')

CREATE VIEW OrderedBookings (interval_start, interval_end, seq)
AS
SELECT B1.booking_start_time,
B1.booking_end_time,
(SELECT COUNT(*)
FROM Bookings AS B2
WHERE B2.booking_start_time < B1.booking_start_time OR
(B2.booking_start_time = B1.booking_start_time AND
B2.booking_end_time < B1.booking_end_time))
FROM Bookings AS B1

-- A DATETIME value can be from date 17530101 to date 99991231
CREATE VIEW BookingGaps (interval_start, interval_end)
AS
SELECT '17530101' AS interval_start, interval_start AS interval_end
FROM OrderedBookings
WHERE seq = 0 AND interval_start > '17530101'
UNION ALL
SELECT OI1.interval_end AS interval_start,
COALESCE(OI2.interval_start, '99991231') AS interval_end
FROM OrderedBookings AS OI1
LEFT OUTER JOIN
OrderedBookings AS OI2
ON OI2.seq = OI1.seq + 1
WHERE OI2.interval_start > OI1.interval_end OR
OI2.seq IS NULL

CREATE VIEW InterestedBookingGaps (interval_start, interval_end)
AS
SELECT CASE WHEN G.interval_start BETWEEN
I.interval_start AND I.interval_end
THEN G.interval_start
ELSE I.interval_start
END,
CASE WHEN G.interval_end BETWEEN
I.interval_start AND I.interval_end
THEN G.interval_end
ELSE I.interval_end
END
FROM BookingGaps AS G
INNER JOIN
InterestedIntervals AS I
ON NOT(I.interval_start >= G.interval_end OR
I.interval_end <= G.interval_start)

-- Booking gaps with respect to interested intervals
SELECT interval_start, interval_end
FROM InterestedBookingGaps
ORDER BY interval_start

interval_start interval_end
2003-09-30 08:00:00.000 2003-09-30 08:30:00.000
2003-09-30 12:30:00.000 2003-09-30 13:00:00.000
2003-09-30 16:30:00.000 2003-09-30 18:00:00.000
2003-09-30 20:00:00.000 2003-09-30 21:00:00.000
2003-09-30 23:30:00.000 2003-09-30 23:59:00.000

-- Free time
SELECT COALESCE(SUM(DATEDIFF(MINUTE, interval_start, interval_end))
/ 60.0, 0) AS hours_free,
COALESCE(SUM(DATEDIFF(MINUTE, interval_start, interval_end)), 0)
AS minutes_free
FROM InterestedBookingGaps

hours_free minutes_free
3.983333 239

Regards,
jag