Find Vacancies

Discussion in 'ASP General' started by Simon Harris, Sep 9, 2004.

  1. Simon Harris

    Simon Harris Guest

    Hi All,

    Ok - I'll confess from the start, this is more about application logic that
    ASP, being an ASP programmer, I guessed you people might be able to help! :)

    I have built a room bookings system, based on ASP/SQL Server. Bookings are
    stored in a table, which holds start date+time and End date+time, amougst
    other info. Bookings can run from 8:30 - midnight, and are always made in 30
    min blocks (e.g. 14:30 - 15:30 etc)

    I now need to devise a page/script that will search for vacancies, so the
    administrator of the system can be on the phone, and enter two dates, then
    find a vacant spot for a client.

    So far, I have done this...

    a) Take two dates, user defined
    b) Loop dates, incrementing day until end date is matched
    c) Within each date, loop again - incrementing minutes by 30 each time
    d) for each loop of 30 mins, run SQL statement ... Select * from bookings
    where startdate = <currentloopdate> <currentlooptime>;
    e) If booking found, slot not available, else available.

    This appears to work, but it doesnt take much math to work out that a check
    over a 30 day span, requires 930 seperate SQL Select statements!!

    As you may expect...its SLOW and draining server resources!

    Has anyone got any better ideas on how I can achive this?

    Cheers!
    Simon.

    --
    -
    * Please reply to group for the benefit of all
    * Found the answer to your own question? Post it!
    * Get a useful reply to one of your posts?...post an answer to another one
    * Search first, post later : http://www.google.co.uk/groups
    * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
    Simon Harris, Sep 9, 2004
    #1
    1. Advertising

  2. Given a little more info about your database structure, and how you wish to
    display the results, I'm sure we could devise a single sql statement to give
    you what you want.

    Bob Barrows

    Simon Harris wrote:
    > Hi All,
    >
    > Ok - I'll confess from the start, this is more about application
    > logic that ASP, being an ASP programmer, I guessed you people might
    > be able to help! :)
    >
    > I have built a room bookings system, based on ASP/SQL Server.
    > Bookings are stored in a table, which holds start date+time and End
    > date+time, amougst other info. Bookings can run from 8:30 - midnight,
    > and are always made in 30 min blocks (e.g. 14:30 - 15:30 etc)
    >
    > I now need to devise a page/script that will search for vacancies, so
    > the administrator of the system can be on the phone, and enter two
    > dates, then find a vacant spot for a client.



    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Sep 9, 2004
    #2
    1. Advertising

  3. Simon Harris

    Simon Harris Guest

    Hi Bob,

    Thanks for your reply, hopefully the following will help.
    The system is somewhat more complex that this, but I think I have provided
    relevent info for this particular problem.

    Regards,
    Simon.

    bookings table
    ---------------
    FromTime (DateTime)
    ToTime (DateTime)
    RoomID (Int) - Foreign key joining to Rooms Table (Info such as capacity
    etc)

    Results should be displayed as follows (In the instance where the user has
    opted to check for vacancies between 01 - 05 Jan 04

    RoomName1
    01/01/2004
    Vacancy: 8:30 - 12:00
    Vacancy: 13:30 - 16:00
    02/01/2004
    Vacancy: 15:00 - 18:00
    Vacancy: 19:30 - 21:30
    03/01/2004
    04/01/2004
    Vacancy: 18:00 - 19:00
    05/01/2004

    RoomName2
    01/01/2004
    02/01/2004
    Vacancy: (You get the idea!)
    Vacancy: (You get the idea!)
    Vacancy: (You get the idea!)
    03/01/2004
    04/01/2004
    Vacancy: (You get the idea!)
    Vacancy: (You get the idea!)
    Vacancy: (You get the idea!)
    Vacancy: (You get the idea!)
    05/01/2004

    RoomName3
    01/01/2004
    02/01/2004
    Vacancy: (You get the idea!)
    Vacancy: (You get the idea!)
    03/01/2004
    Vacancy: (You get the idea!)
    Vacancy: (You get the idea!)
    Vacancy: (You get the idea!)
    Vacancy: (You get the idea!)
    04/01/2004
    Vacancy: (You get the idea!)
    05/01/2004


    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Given a little more info about your database structure, and how you wish
    > to
    > display the results, I'm sure we could devise a single sql statement to
    > give
    > you what you want.
    >
    > Bob Barrows
    >
    > Simon Harris wrote:
    >> Hi All,
    >>
    >> Ok - I'll confess from the start, this is more about application
    >> logic that ASP, being an ASP programmer, I guessed you people might
    >> be able to help! :)
    >>
    >> I have built a room bookings system, based on ASP/SQL Server.
    >> Bookings are stored in a table, which holds start date+time and End
    >> date+time, amougst other info. Bookings can run from 8:30 - midnight,
    >> and are always made in 30 min blocks (e.g. 14:30 - 15:30 etc)
    >>
    >> I now need to devise a page/script that will search for vacancies, so
    >> the administrator of the system can be on the phone, and enter two
    >> dates, then find a vacant spot for a client.

    >
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Simon Harris, Sep 9, 2004
    #3
  4. Just a little more information about how the data is stored (I should have
    asked for a few rows of sample data .. ). You said bookings " ... are always
    made in 30 min blocks (e.g. 14:30 - 15:30 etc)" Does that mean this
    particular booking would be stored in two rows?

    14:30 15:00 1
    15:00 15:30 1

    Or would it be contained in a single row (making the problem more complex)?

    14:30 15:30 1

    Bob Barrows

    Simon Harris wrote:
    > Hi Bob,
    >
    > Thanks for your reply, hopefully the following will help.
    > The system is somewhat more complex that this, but I think I have
    > provided relevent info for this particular problem.
    >
    > Regards,
    > Simon.
    >
    > bookings table
    > ---------------
    > FromTime (DateTime)
    > ToTime (DateTime)
    > RoomID (Int) - Foreign key joining to Rooms Table (Info such as
    > capacity etc)
    >
    > Results should be displayed as follows (In the instance where the
    > user has opted to check for vacancies between 01 - 05 Jan 04
    >
    > RoomName1
    > 01/01/2004
    > Vacancy: 8:30 - 12:00
    > Vacancy: 13:30 - 16:00
    > 02/01/2004
    > Vacancy: 15:00 - 18:00
    > Vacancy: 19:30 - 21:30
    > 03/01/2004
    > 04/01/2004
    > Vacancy: 18:00 - 19:00
    > 05/01/2004
    >
    > RoomName2
    > 01/01/2004
    > 02/01/2004
    > Vacancy: (You get the idea!)
    > Vacancy: (You get the idea!)
    > Vacancy: (You get the idea!)
    > 03/01/2004
    > 04/01/2004
    > Vacancy: (You get the idea!)
    > Vacancy: (You get the idea!)
    > Vacancy: (You get the idea!)
    > Vacancy: (You get the idea!)
    > 05/01/2004
    >
    > RoomName3
    > 01/01/2004
    > 02/01/2004
    > Vacancy: (You get the idea!)
    > Vacancy: (You get the idea!)
    > 03/01/2004
    > Vacancy: (You get the idea!)
    > Vacancy: (You get the idea!)
    > Vacancy: (You get the idea!)
    > Vacancy: (You get the idea!)
    > 04/01/2004
    > Vacancy: (You get the idea!)
    > 05/01/2004
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> Given a little more info about your database structure, and how you
    >> wish to
    >> display the results, I'm sure we could devise a single sql statement
    >> to give
    >> you what you want.
    >>
    >> Bob Barrows
    >>
    >> Simon Harris wrote:
    >>> Hi All,
    >>>
    >>> Ok - I'll confess from the start, this is more about application
    >>> logic that ASP, being an ASP programmer, I guessed you people might
    >>> be able to help! :)
    >>>
    >>> I have built a room bookings system, based on ASP/SQL Server.
    >>> Bookings are stored in a table, which holds start date+time and End
    >>> date+time, amougst other info. Bookings can run from 8:30 -
    >>> midnight, and are always made in 30 min blocks (e.g. 14:30 - 15:30
    >>> etc)
    >>>
    >>> I now need to devise a page/script that will search for vacancies,
    >>> so the administrator of the system can be on the phone, and enter
    >>> two dates, then find a vacant spot for a client.

    >>
    >>
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get
    >> a quicker response by posting to the newsgroup.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Sep 9, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andy Robinson

    Python vacancies at ReportLab, London

    Andy Robinson, Jan 31, 2004, in forum: Python
    Replies:
    0
    Views:
    465
    Andy Robinson
    Jan 31, 2004
  2. Hot Vacancies (12th July 2007)

    , Jul 12, 2007, in forum: C Programming
    Replies:
    0
    Views:
    270
  3. Replies:
    0
    Views:
    298
  4. Replies:
    0
    Views:
    267
  5. Replies:
    0
    Views:
    2,009
Loading...

Share This Page