MySQL Create every Sun for date = xx
-
Hi, I am using below MySQL stored procedure to add a new job order into MySql table. I would like to do the following modification in the code: I want to add an additional parameter called param_max_auto_weekday then I want to check the WeekDay of the param_job_order_date and repeat the code until reaching param_max_auto_weekday so if the WeekDay(param_job_order_date) = Sunday then It should INSERT INTO job_orders for every Sunday in the week until param_max_auto_weekday Here is the code... with Thanks:
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_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
INSERT INTO job_orders (customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_contract, contract_id, total_hours, created_date, created_user) VALUES (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_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
SET param_record_identity = LAST_INSERT_ID();UPDATE customers SET allow\_delete = FALSE WHERE customer\_id = param\_customer\_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.");END
Technology News @ www.JassimRahma.com
-
Hi, I am using below MySQL stored procedure to add a new job order into MySql table. I would like to do the following modification in the code: I want to add an additional parameter called param_max_auto_weekday then I want to check the WeekDay of the param_job_order_date and repeat the code until reaching param_max_auto_weekday so if the WeekDay(param_job_order_date) = Sunday then It should INSERT INTO job_orders for every Sunday in the week until param_max_auto_weekday Here is the code... with Thanks:
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_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
INSERT INTO job_orders (customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_contract, contract_id, total_hours, created_date, created_user) VALUES (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_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
SET param_record_identity = LAST_INSERT_ID();UPDATE customers SET allow\_delete = FALSE WHERE customer\_id = param\_customer\_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.");END
Technology News @ www.JassimRahma.com
-
Hi, I am using below MySQL stored procedure to add a new job order into MySql table. I would like to do the following modification in the code: I want to add an additional parameter called param_max_auto_weekday then I want to check the WeekDay of the param_job_order_date and repeat the code until reaching param_max_auto_weekday so if the WeekDay(param_job_order_date) = Sunday then It should INSERT INTO job_orders for every Sunday in the week until param_max_auto_weekday Here is the code... with Thanks:
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_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
INSERT INTO job_orders (customer_id, cleaner_id, job_order_date, job_order_date_to, start_time, end_time, job_order_note, is_contract, contract_id, total_hours, created_date, created_user) VALUES (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_contract, param_contract_id, param_total_hours, NOW(), param_created_user);
SET param_record_identity = LAST_INSERT_ID();UPDATE customers SET allow\_delete = FALSE WHERE customer\_id = param\_customer\_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.");END
Technology News @ www.JassimRahma.com
I wonder, does MySQL only support lower case? That is almost unreadable. I would do that differently, create a query based on the number of days to be inserted, and insert in one query rather than a loop.
Never underestimate the power of human stupidity RAH
-
yeah but my questions is how to start with it? if today is 10th April 2014 and I want list of every Sunday until 10th April 2020? Do you I need to loop?! what's the best way in terms of performance?
Technology News @ www.JassimRahma.com