why looping is not starting from my start date?
-
Hi, I am using this code to loop from start date to end date and add job orders into MySQL table. I only have one problem here. If My start date is 14 April 2014 it will insert records from 15 April 2014 so it's skipping my start date. can anyone help please... here is the stored procedure..
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_job_order_date_to date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_recurring bit, IN param_max_recurring_date date, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11, 6), IN param_created_user int, OUT param_record_identity int)
BEGIN
UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;INSERT INTO job\_orders (history\_status, status\_user, status\_date, customer\_id, cleaner\_id, job\_order\_date, job\_order\_date\_to, start\_time, end\_time, job\_order\_note, is\_recurring, max\_recurring\_date, is\_contract, contract\_id, total\_hours, created\_date, created\_user) VALUES ('New', param\_created\_user, NOW(), param\_customer\_id, param\_cleaner\_id, param\_job\_order\_date, param\_job\_order\_date\_to, param\_start\_time, param\_end\_time, param\_job\_order\_note, param\_is\_recurring, param\_max\_recurring\_date, param\_is\_contract, param\_contract\_id, param\_total\_hours, NOW(), param\_created\_user); SET param\_record\_identity = LAST\_INSERT\_ID(); IF (param\_is\_contract = TRUE) THEN BEGIN UPDATE job\_orders SET job\_order\_status = 6 WHERE job\_order\_id = param\_record\_identity; UPDATE contracts SET remaining\_hours = remaining\_hours - param\_total\_hours WHERE contract\_id = param\_contract\_id; END; END IF; IF (param\_is\_recurring = TRUE) THEN BEGIN REPEAT SET param\_job\_order\_date = DATE\_ADD(param\_job\_order\_date, INTERVAL 7 DAY); -- insert recurring job orders; INSERT INTO job\_orders (history\_status, status\_user, status\_date, customer\_id, cleaner\_id, job\_order\_date, job\_order\_date\_to, start\_time, end\_time, job\_order\_note, is\_recurring, max\_recurring\_date, is\_contract, contract\_id, total\_hours, created\_date, created\_user) VALUES ('New', param\_created\_user, NOW(), param\_customer\_id, param\_cleaner\_id, param\_job\_order\_date, param\_job\_order\_date\_to, param\_start\_time, param\_end\_time, param\_job\_order\_note, param\_is\_recurring, param\_max\_recurring\_date, param\_is\_contract, param\_contract\_id, param\_total\_hours, NOW(), param\_created\_user); SET param\_record\_i -
Hi, I am using this code to loop from start date to end date and add job orders into MySQL table. I only have one problem here. If My start date is 14 April 2014 it will insert records from 15 April 2014 so it's skipping my start date. can anyone help please... here is the stored procedure..
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_job_order_date_to date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_recurring bit, IN param_max_recurring_date date, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11, 6), IN param_created_user int, OUT param_record_identity int)
BEGIN
UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;INSERT INTO job\_orders (history\_status, status\_user, status\_date, customer\_id, cleaner\_id, job\_order\_date, job\_order\_date\_to, start\_time, end\_time, job\_order\_note, is\_recurring, max\_recurring\_date, is\_contract, contract\_id, total\_hours, created\_date, created\_user) VALUES ('New', param\_created\_user, NOW(), param\_customer\_id, param\_cleaner\_id, param\_job\_order\_date, param\_job\_order\_date\_to, param\_start\_time, param\_end\_time, param\_job\_order\_note, param\_is\_recurring, param\_max\_recurring\_date, param\_is\_contract, param\_contract\_id, param\_total\_hours, NOW(), param\_created\_user); SET param\_record\_identity = LAST\_INSERT\_ID(); IF (param\_is\_contract = TRUE) THEN BEGIN UPDATE job\_orders SET job\_order\_status = 6 WHERE job\_order\_id = param\_record\_identity; UPDATE contracts SET remaining\_hours = remaining\_hours - param\_total\_hours WHERE contract\_id = param\_contract\_id; END; END IF; IF (param\_is\_recurring = TRUE) THEN BEGIN REPEAT SET param\_job\_order\_date = DATE\_ADD(param\_job\_order\_date, INTERVAL 7 DAY); -- insert recurring job orders; INSERT INTO job\_orders (history\_status, status\_user, status\_date, customer\_id, cleaner\_id, job\_order\_date, job\_order\_date\_to, start\_time, end\_time, job\_order\_note, is\_recurring, max\_recurring\_date, is\_contract, contract\_id, total\_hours, created\_date, created\_user) VALUES ('New', param\_created\_user, NOW(), param\_customer\_id, param\_cleaner\_id, param\_job\_order\_date, param\_job\_order\_date\_to, param\_start\_time, param\_end\_time, param\_job\_order\_note, param\_is\_recurring, param\_max\_recurring\_date, param\_is\_contract, param\_contract\_id, param\_total\_hours, NOW(), param\_created\_user); SET param\_record\_iSo I had a hard time finding where your loop started, so I can't give you details on why its happening. However I can give you a tried and true method to find the issue (its worked for over 30 years for me) Play computer: Take a few sheets of paper and write down what you "expect" to happen in plain English. Then write down the algorithm your code is following in plain English. Compare - figure out the mis-match, you may need to search vendor docs to find the "gotcha" Still no luck finding out whats going wrong? Take a small record set and run it through your written algorithm. 99.9999% of the time this method works! Good luck
-
Hi, I am using this code to loop from start date to end date and add job orders into MySQL table. I only have one problem here. If My start date is 14 April 2014 it will insert records from 15 April 2014 so it's skipping my start date. can anyone help please... here is the stored procedure..
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_job_order_date_to date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_recurring bit, IN param_max_recurring_date date, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11, 6), IN param_created_user int, OUT param_record_identity int)
BEGIN
UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;INSERT INTO job\_orders (history\_status, status\_user, status\_date, customer\_id, cleaner\_id, job\_order\_date, job\_order\_date\_to, start\_time, end\_time, job\_order\_note, is\_recurring, max\_recurring\_date, is\_contract, contract\_id, total\_hours, created\_date, created\_user) VALUES ('New', param\_created\_user, NOW(), param\_customer\_id, param\_cleaner\_id, param\_job\_order\_date, param\_job\_order\_date\_to, param\_start\_time, param\_end\_time, param\_job\_order\_note, param\_is\_recurring, param\_max\_recurring\_date, param\_is\_contract, param\_contract\_id, param\_total\_hours, NOW(), param\_created\_user); SET param\_record\_identity = LAST\_INSERT\_ID(); IF (param\_is\_contract = TRUE) THEN BEGIN UPDATE job\_orders SET job\_order\_status = 6 WHERE job\_order\_id = param\_record\_identity; UPDATE contracts SET remaining\_hours = remaining\_hours - param\_total\_hours WHERE contract\_id = param\_contract\_id; END; END IF; IF (param\_is\_recurring = TRUE) THEN BEGIN REPEAT SET param\_job\_order\_date = DATE\_ADD(param\_job\_order\_date, INTERVAL 7 DAY); -- insert recurring job orders; INSERT INTO job\_orders (history\_status, status\_user, status\_date, customer\_id, cleaner\_id, job\_order\_date, job\_order\_date\_to, start\_time, end\_time, job\_order\_note, is\_recurring, max\_recurring\_date, is\_contract, contract\_id, total\_hours, created\_date, created\_user) VALUES ('New', param\_created\_user, NOW(), param\_customer\_id, param\_cleaner\_id, param\_job\_order\_date, param\_job\_order\_date\_to, param\_start\_time, param\_end\_time, param\_job\_order\_note, param\_is\_recurring, param\_max\_recurring\_date, param\_is\_contract, param\_contract\_id, param\_total\_hours, NOW(), param\_created\_user); SET param\_record\_i -
In the job_order table when inserted.
Technology News @ www.JassimRahma.com
-
In the job_order table when inserted.
Technology News @ www.JassimRahma.com
-
And that is supposed to help? Please edit your question and indicate clearly where the various variables are (and their content) and which line of code causes the problem.
Which is what I was trying to tell him. Play the SQL parser, read the date in the table. 9 times out of ten when you compare dates there is something with the full timestamp in the field that is messed up. Or maybe the field doesn't have a full date saved which causes comparisons to fail when you think they won't. For example 22/04/2014 10:00:00 is greater than 22/04/2014 00:01:00 but depending on the way SQL comparisons work (I'm reaching into ancient history) it is not greater than 22/04/2014 because the parser interprets 22/04/2014 into 22/04/2014 12:00:00 sometimes you have to use tochar function to compare dates