Professional Web Applications Themes

Selecting by time slots - Microsoft SQL / MS SQL Server

Hi, I'm writing a conference room booking system for our corporate intranet and I want to create a stored procedure that takes the start datetime, finish datetime and room number as input parameters and then either returns a table showing the conflicting reservations or adds the data into the table. The problem that I have is encapsulating the logic to find if there are any conflicting reservations. I've tried several methods with temp tables and all sorts but one always slips through and at this point my head is nearly melted :-) Has anyone done something like this before ? ...

  1. #1

    Default Selecting by time slots

    Hi,

    I'm writing a conference room booking system for our corporate intranet and
    I want to create a stored procedure that takes the start datetime, finish
    datetime and room number as input parameters and then either returns a table
    showing the conflicting reservations or adds the data into the table.

    The problem that I have is encapsulating the logic to find if there are any
    conflicting reservations. I've tried several methods with temp tables and
    all sorts but one always slips through and at this point my head is nearly
    melted :-)

    Has anyone done something like this before ? If so help !!!!

    Thanks,

    Matt Lemon.




    Matt Guest

  2. #2

    Default Re: Selecting by time slots

    Assuming you have the checks in place (start_time < finish_time and such),
    you can put the following in your stored procedure:

    IF EXISTS(SELECT NULL FROM room_bookings WHERE room_number = room_number
    AND((start_time >=start_time AND start_time < finish_time)
    OR (finish_time > start_time AND finish_time <= finish_time)))
    SELECT room_number, start_time, finish_time FROM room_bookings WHERE
    room_number = room_number
    AND((start_time >=start_time AND start_time < finish_time)
    OR (finish_time > start_time AND finish_time <= finish_time)))
    ELSE
    INSERT INTO room_bookings (room_number, start_time, finish_time)
    VALUES(room_number, start_time, finish_time)


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Matt Lemon" <ie> wrote in message
    news:bflqhu$gvl$esat.net... 
    and 
    table 
    any 


    Jacco Guest

  3. #3

    Default Re: Selecting by time slots

    Your logic is faulty in the following instance, Jacco:

    CREATE TABLE room_bookings (room_number INTEGER NOT NULL, start_time
    DATETIME NOT NULL, finish_time DATETIME NOT NULL, PRIMARY KEY (room_number,
    start_time), CHECK (start_time<finish_time))

    INSERT INTO room_bookings VALUES
    (1,'2003-01-01T09:00:00','2003-01-01T10:00:00')

    DECLARE room_number INTEGER, start_time DATETIME, finish_time DATETIME
    SET room_number = 1
    SET start_time = '2003-01-01T08:00:00'
    SET finish_time = '2003-01-01T11:00:00'

    IF EXISTS(SELECT NULL FROM room_bookings WHERE room_number = room_number
    AND((start_time >=start_time AND start_time < finish_time)
    OR (finish_time > start_time AND finish_time <= finish_time)))
    SELECT room_number, start_time, finish_time FROM room_bookings WHERE
    room_number = room_number
    AND((start_time >=start_time AND start_time < finish_time)
    OR (finish_time > start_time AND finish_time <= finish_time))
    ELSE
    INSERT INTO room_bookings (room_number, start_time, finish_time)
    VALUES(room_number, start_time, finish_time)

    Result:

    room_number start_time finish_time
    ----------- ------------------------- -------------------------
    1 2003-01-01 08:00:00.000 2003-01-01 11:00:00.000 -- Conflicting
    Booking!
    1 2003-01-01 09:00:00.000 2003-01-01 10:00:00.000

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



    David Guest

  4. #4

    Default Re: Selecting by time slots

    CORRECTION:

    Add

    ... AND roomnumber = roomnumber

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



    David Guest

  5. #5

    Default Re: Selecting by time slots

    Seems to work for me !

    Thanks David.

    Matt

    "David Portas" <org> wrote in message
    news:phx.gbl... 


    Matt Guest

  6. #6

    Default Re: Selecting by time slots

    Hmmm, I think I forgot that the last time I answered a similar question as
    well, and I still don't seem to learn :$
    Thanks for the correction.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "David Portas" <org> wrote in message
    news:phx.gbl... 
    (room_number, 
    Conflicting 


    Jacco Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139