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:
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. how can I fix this please? http://www.JassimRahma.com[^] http://www.rmc.bh[^] http://www.ume.bh[^] http://www.xoompage.com[^] http://www.volow.com[^] http://www.curesoftware.com[^]
Technology News @ www.JassimRahma.com
-
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:
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. how can I fix this please? http://www.JassimRahma.com[^] http://www.rmc.bh[^] http://www.ume.bh[^] http://www.xoompage.com[^] http://www.volow.com[^] http://www.curesoftware.com[^]
Technology News @ www.JassimRahma.com
Seems like you need to check the end time ...
AND end_time BETWEEN param_start_time AND param_end_time)
Maybe the check for the end time needs to be
OR end_time BETWEEN param_start_time AND param_end_time
-
Seems like you need to check the end time ...
AND end_time BETWEEN param_start_time AND param_end_time)
Maybe the check for the end time needs to be
OR end_time BETWEEN param_start_time AND param_end_time
BETWEEN always uses AND not OR
Technology News @ www.JassimRahma.com
-
BETWEEN always uses AND not OR
Technology News @ www.JassimRahma.com
What I meant to say is the following ...
OR end_time BETWEEN param_start_time AND param_end_time
Notice where I put the "OR"
-
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:
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. how can I fix this please? http://www.JassimRahma.com[^] http://www.rmc.bh[^] http://www.ume.bh[^] http://www.xoompage.com[^] http://www.volow.com[^] http://www.curesoftware.com[^]
Technology News @ www.JassimRahma.com
You've posted a list of six links in the bottom of your question, none of which relate to your question, and which don't appear to be part of your signature. I understand the desire to show off your work, but is this really the best way to do it?
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
What I meant to say is the following ...
OR end_time BETWEEN param_start_time AND param_end_time
Notice where I put the "OR"
I tried this but didn't work too!
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;
Please help...
Technology News @ www.JassimRahma.com
