Question

我需要书面问询,以找到会议室预订系统的可用时间。

我有一个大会堂桌子,储存大会堂的细节和大会堂的桌子,储存图书开端和时间。

Hall - HallId - Name

HallBooking - HallBookingId - HallId - BookingPersonName - StartDateTime - EndDateTime

用户可以通过规定一个起始时间和结束日期来寻找大厅的可用性,询问应当收集并表明该大厅是否可供使用。

如果时间档为NOT,那么询问应收集当天相同时间的可用位置,并向用户显示。

Thanks in advance Anz

Answer 1

这里有一份可能由你开始的试稿。

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
        )

友情链接