Calculating Time Difference

[Shaun]

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

[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_T ime, 112) as Date,
DateDiff(minute , Booking_Start_T ime, Booking_End_Tim e) as
BookTime
from YourTable
) as Tmp
group by Date

Shervin

"Shaun" <sh***@mania.pl us.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_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

[John Gilson]

"Shaun" <sh***@mania.pl us.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_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

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_t ime DATETIME NOT NULL,
booking_end_tim e DATETIME NOT NULL,
CHECK (booking_end_ti me >= booking_start_t ime),
PRIMARY KEY (booking_start_ time, booking_end_tim e)
)

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

-- Interested intervals can't overlap
CREATE TABLE InterestedInter vals
(
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 InterestedInter vals (interval_start , interval_end)
VALUES ('20030930 8:00AM', '20030930 1:00PM')
INSERT INTO InterestedInter vals (interval_start , interval_end)
VALUES ('20030930 2:00PM', '20030930 6:00PM')
INSERT INTO InterestedInter vals (interval_start , interval_end)
VALUES ('20030930 8:00PM', '20030930 11:59PM')

CREATE VIEW OrderedBookings (interval_start , interval_end, seq)
AS
SELECT B1.booking_star t_time,
B1.booking_end_ time,
(SELECT COUNT(*)
FROM Bookings AS B2
WHERE B2.booking_star t_time < B1.booking_star t_time OR
(B2.booking_sta rt_time = B1.booking_star t_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_en d AS interval_start,
COALESCE(OI2.in terval_start, '99991231') AS interval_end
FROM OrderedBookings AS OI1
LEFT OUTER JOIN
OrderedBookings AS OI2
ON OI2.seq = OI1.seq + 1
WHERE OI2.interval_st art > OI1.interval_en d OR
OI2.seq IS NULL

CREATE VIEW InterestedBooki ngGaps (interval_start , interval_end)
AS
SELECT CASE WHEN G.interval_star t BETWEEN
I.interval_star t AND I.interval_end
THEN G.interval_star t
ELSE I.interval_star t
END,
CASE WHEN G.interval_end BETWEEN
I.interval_star t AND I.interval_end
THEN G.interval_end
ELSE I.interval_end
END
FROM BookingGaps AS G
INNER JOIN
InterestedInter vals AS I
ON NOT(I.interval_ start >= G.interval_end OR
I.interval_end <= G.interval_star t)

-- Booking gaps with respect to interested intervals
SELECT interval_start, interval_end
FROM InterestedBooki ngGaps
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(DA TEDIFF(MINUTE, interval_start, interval_end))
/ 60.0, 0) AS hours_free,
COALESCE(SUM(DA TEDIFF(MINUTE, interval_start, interval_end)), 0)
AS minutes_free
FROM InterestedBooki ngGaps

hours_free minutes_free
3.983333 239

Regards,
jag