-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `chan_insert_date_by_starttime`()
BEGIN
DECLARE var_id decimal(18,0);
DECLARE var_issue decimal(18,0);
DECLARE var_date datetime ;
DECLARE cur1 CURSOR FOR
SELECT issue, datevalue
FROM jira.customfieldvalue
where customfield in (10006,10007)
and issue in (
SELECT distinct issue
FROM jira.customfieldvalue
where customfield in (10007)
)
and issue not in (
SELECT distinct issue
FROM jira.customfieldvalue
where customfield in (10006)
);
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO var_issue, var_date;
select (max(id) + 1) into var_id from jira.customfieldvalue ;
INSERT INTO jira.customfieldvalue(id,issue, customfield,datevalue)
VALUES (var_id,var_issue,10006, var_date);
END LOOP;
CLOSE cur1;
END
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `chan_insert_date_by_created`()
BEGIN
DECLARE var_id decimal(18,0);
DECLARE var_issue decimal(18,0);
DECLARE var_date datetime ;
DECLARE cur1 CURSOR FOR
SELECT id, created FROM jira.jiraissue where id in
( SELECT issue
FROM jira.customfieldvalue
where customfield in (10000)
and issue not in (
SELECT distinct issue
FROM jira.customfieldvalue
where customfield in (10007)
)
and issue not in (
SELECT distinct issue
FROM jira.customfieldvalue
where customfield in (10006)
)
) ;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO var_issue, var_date;
select (max(id) + 1) into var_id from jira.customfieldvalue ;
INSERT INTO jira.customfieldvalue(id,issue, customfield,datevalue)
VALUES (var_id,var_issue,10006, var_date);
END LOOP;
CLOSE cur1;
END
-----------------------------------------------------
Silence, the way to avoid many problems;
Smile, the way to solve many problems;