how to check available cleaner
Results 1 to 4 of 4

Thread: how to check available cleaner

  1. #1
    Senior Member
    Join Date
    Oct 2011
    Posts
    267

    how to check available cleaner

    Hi,

    I have a job_orders table in my database with cleaner_id, job_order_date, start_time and end_time.

    I am inserting job orders in the database.

    I would like to check before inserting for available staff

    I tried the following:

    Code:
    SELECT cleaner_id, cleaner_name FROM cleaners WHERE cleaner_id NOT IN (SELECT cleaner_id FROM job_orders WHERE job_order_date = param_job_order_date AND start_time BETWEEN param_start_time AND param_end_time) AND is_active = TRUE ORDER BY cleaner_name;
    then added a record with start_time = 10:00 and end_time = 13:00

    when I tried to run the code again with start_time = 11:00 and end_time = 13:00 it works fine

    but when I try start_time = 11:00 and end_time = 14:00 it will not determine that staff is already booked for 10:00 TO 13:00.

    I also tried this but didn't work:

    Code:
    SELECT cleaner_id, cleaner_name FROM cleaners WHERE cleaner_id NOT IN (SELECT cleaner_id FROM job_orders WHERE job_order_date = param_job_order_date AND start_time BETWEEN param_start_time AND param_end_time OR end_time BETWEEN param_start_time AND param_end_time) AND is_active = TRUE ORDER BY cleaner_name;
    how can I fix this please?
    www.JassimRahma.com
    www.ume.bh
    www.xoompage.com
    www.volow.com
    www.curesoftware.com

  2. #2
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,647
    First off, as has been previously stated in some former thread of yours, using a left join and proper join conditions will be faster and considerably so.

    Position two markers on the table in front of you, one representing a wanted start time and the other a wanted end time. There is no need to worry about "what time" they would represent. Just any start and end times. Then take two different markers and let these represent an existing start time of a cleaner and its matching end time. When they overlap you have a conflict. When they are non-overlapping the cleaner is available. Use this abstraction to determine what fields to compare in the database.

  3. #3
    Senior Member
    Join Date
    Oct 2011
    Posts
    267
    Quote Originally Posted by johanafm View Post
    First off, as has been previously stated in some former thread of yours, using a left join and proper join conditions will be faster and considerably so.

    Position two markers on the table in front of you, one representing a wanted start time and the other a wanted end time. There is no need to worry about "what time" they would represent. Just any start and end times. Then take two different markers and let these represent an existing start time of a cleaner and its matching end time. When they overlap you have a conflict. When they are non-overlapping the cleaner is available. Use this abstraction to determine what fields to compare in the database.
    but even if I use JOIN the problem will still exists because it's with the start and end time, with the <, > or the BETWEEN.

    Could you please explain it more..

    Thanks,
    Jassim

  4. #4
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,647
    Let 1 and 2 be desired start and end time. Let A and B be existing start and end time for a cleaner

    Cleaner is free
    Code:
             1    2
    A   B
    Cleaner is free
    Code:
             1    2
                       A   B
    Covering all of the above, which expressed with words would be: match all of a cleaner's existing bookings that are not in the desired range
    B < 1 OR A > 2
    or translated to fieldnames (most likley not matching your actual names though)
    cleaner.endtime < wanted.starttime OR cleaner.starttime > wanted.endtime.

    Note: The above logic expression is enough to deduce the logical expression for when user is not free, since
    !(B < 1 OR A > 2) can be changed into
    B > 1 AND A < 2

    But let's examine it anyway
    Cleaner is not free
    Code:
           1       2
       A      B
    B > 1 and A < 2 (as predicted)

    Cleaner is not free
    Code:
           1       2
                 A      B
    B > 1 and A < 2 (as predicted)

    Cleaner is not free
    Code:
       1          2
          A   B
    B > 1 and A < 2 (as predicted)

    To understand how to use these results in combination with a left join, you need to first understand why your previous question resulted in the best possible query to solve it was using a left join (which you allready had) and adding a condition that the the joined table contained null (on any field)
    To quickly reiterate. This select all cleaners that have no bookings at all.
    Code:
    SELECT fields
    FROM cleaner c
    INNER JOIN bookings b ON b.cleaner_id = c.id
    WHERE b.id IS NULL
    Thus, perform a left join with the added join condition that there is an existing bookings. The it's only the cleaners that do not have such a booking that will have null fields in the joined table. Which obviously happens to be those that are available during those times.

    Code:
    FROM cleaner c
    LEFT JOIN bookings b WHERE b.cleaner_id = c.id AND b.end_time > @wanted_start_time AND b.start_time < @wanted_end_time
    WHERE b.id IS NULL

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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