why looping is not starting from my start date?

[Jassm Rahma]

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_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;

-- add event log;
CALL sp_add_event_log("JOBORDER", param_record_identity, param_created_user, "Job order was created.");
CALL sp_add_event_log("CUSTOMER", param_customer_id, param_created_user, "Job order was created.");
UNTIL param_job_order_date >= param_max_recurring_date
END REPEAT;
END;
END IF;
END