这里有一份可能由你开始的试稿。
DECLARE @Hall TABLE
(
HallID INT PRIMARY KEY ,
HallName VARCHAR(1)
)
DECLARE @HallBooking TABLE
(
HallBookingID INT PRIMARY KEY ,
HallID INT ,
BookingPersonName VARCHAR(10) ,
StartDateTime DATETIME ,
EndDateTime DATETIME
)
INSERT @Hall
SELECT 1 ,
A
UNION ALL
SELECT 2 ,
B
UNION ALL
SELECT 3 ,
C
--TEST 1 Check for available Hall
DECLARE @HallID INT ,
@StartDateTime DATETIME ,
@EndDateTime DATETIME
DECLARE @Status VARCHAR(6)
SELECT @HallID = 1 ,
@StartDateTime = 20091021 8:00:00 ,
@EndDateTime = 20091021 12:00:00
--INSERT TestReservationCode here
SELECT @Status = Open
FROM @Hall h
WHERE h.HallID = @HallID
AND NOT EXISTS ( SELECT *
FROM @HallBooking hb
WHERE h.HallID = hb.HallID
AND ( @StartDateTime >= hb.StartDateTime
AND @StartDateTime < hb.EndDateTime
OR @EndDateTime > hb.StartDateTime
AND @EndDateTime <= hb.EndDateTime
) )
IF @Status IS NULL
BEGIN
SELECT @Status = Closed
SELECT *
FROM @Hall h
WHERE h.HallID <> @HallID
AND NOT EXISTS ( SELECT *
FROM @HallBooking hb
WHERE h.HallID = hb.HallID
AND ( @StartDateTime >= hb.StartDateTime
AND @StartDateTime < hb.EndDateTime
OR @EndDateTime > hb.StartDateTime
AND @EndDateTime <= hb.EndDateTime
) )
END
SELECT @Status
为了测试保留状况,在上述INSERT评论之后插入以下代码,并重新加以管理。
--TestReservationCode
INSERT INTO @HallBooking
( HallBookingID ,
HallID ,
BookingPersonName ,
StartDateTime ,
EndDateTime
)
VALUES ( 1 , -- HallBookingID - int
1 , -- HallID - int
Stuart , -- BookingPersonName - varchar(10)
2009-10-21 10:00 , -- StartDateTime - datetime
2009-10-21 12:00 -- EndDateTime - datetime
)
INSERT INTO @HallBooking
( HallBookingID ,
HallID ,
BookingPersonName ,
StartDateTime ,
EndDateTime
)
VALUES ( 2 , -- HallBookingID - int
2 , -- HallID - int
Stuart , -- BookingPersonName - varchar(10)
2009-10-21 10:00 , -- StartDateTime - datetime
2009-10-21 12:00 -- EndDateTime - datetime
)