Update differents fields based on criterias in MySQL

Question

I store in my DB the demands some users can do. The demands can have differents status (stored as events), such as in progress, finished, waiting, and so on (there's 30ish differents status). The demands have differents deadlines corresponding of differents steps of the treatment.

I need to "freeze" some deadlines of the demands, if their current status belongs to a list of pre-defined ones.

In example :

• If a demand has the status "A", I have to "freeze" the deadline 2 to 5.
• If the status is "B" or "C", I have to "freeze" the deadline 3 to 5.
• If the status is "D", I have to "freeze" the deadline 4 and 5.

I plan to use an EVENT that runs every day, at 19:00 to update (add 1 day) the differents deadlines of the concerned demands.

Table structures :

Table demand

id | someDatas | deadline1 | deadline2 | deadline3 | deadline4 | deadline5
---+-----------+-----------+-----------+-----------+-----------+-----------
   |           |           |           |           |           |

Table status

id | name
---+-----
   |

Table events

id | id_demand | someOthersDatas | id_status
---+-----------+-----------------+----------
   |           |                 |

I wrote a query to get the demands corresponding of a list of status :

SELECT dem.*, st.`name` as 'statusName'
FROM `status` st
INNER JOIN `events` eve
    ON eve.id_status = st.id
INNER JOIN `demand` dem
    ON eve.id_demand = dem.id
WHERE st.`name` IN ('A', 'B', 'C', 'D')
    AND eve.id IN
    (
        SELECT MAX(even.id) ev
        FROM `demand` de
        INNER JOIN `events` even
            ON even.id_demand = de.id
        GROUP BY de.id
    );

This query works perfectly and I can get the desired informations for my treatment, I have the id of the demands, its deadlines and the name of the current status.

I don't mind storing this result in a temporary table such as :

DROP TEMPORARY TABLE IF EXISTS pendingDemands;
CREATE TEMPORARY TABLE IF NOT EXISTS pendingDemands
SELECT /* the query shown above */

To make sure the day I want to add to the deadline is valid (= not a day off) I wrote a function that calculate the next valid day :

DELIMITER //
DROP FUNCTION IF EXISTS `get_next_valid_date`;
CREATE FUNCTION `get_next_valid_date`(MyDate DATETIME) RETURNS DATETIME
BEGIN
    REPEAT
        SET MyDate = (DATE_ADD(MyDate, INTERVAL 1 DAY));
        SET @someCondition = (select isDayOff(MyDate));
    UNTIL (@someCondition = 0) END REPEAT;
    RETURN MyDate;
END//

This function works perfectly and I get the expected results, and isDayOff() don't need to be detailed.

My problem is that I don't know how to use them (the temporary table pendingDemands and the function get_next_valid_date) together to update the table demand, I'm not skilled enough in SQL to build such pretty UPDATE query.

Any direction I could take?

Answer 1

I finally found a work around based on this answer

I created a stored procedure in which I'm using a cursor storing the query I was using to feed the pendingDemands temporary table.

Then, I looped over that cursor and used a CASE WHEN statement to determine the values to modify :

DELIMITER $$
DROP PROCEDURE IF EXISTS `freezePendingDeadlines` $$
CREATE PROCEDURE `freezePendingDeadlines`()
BEGIN
    -- from http://stackoverflow.com/questions/35858541/call-a-stored-procedure-from-the-declare-statement-when-using-cursors-in-mysql

    -- declare the program variables where we'll hold the values we're sending into the procedure;
    -- declare as many of them as there are input arguments to the second procedure,
    -- with appropriate data types.

    DECLARE p_id INT DEFAULT 0;
    DECLARE pT2P DATETIME DEFAULT NULL;
    DECLARE pT3P DATETIME DEFAULT NULL;
    DECLARE pT4P DATETIME DEFAULT NULL;
    DECLARE pT5P DATETIME DEFAULT NULL;
    DECLARE pstatusName VARCHAR(255) DEFAULT NULL;

    -- we need a boolean variable to tell us when the cursor is out of data

    DECLARE done TINYINT DEFAULT FALSE;

    -- declare a cursor to select the desired columns from the desired source table1
    -- the input argument (which you might or might not need) is used in this example for row selection

    DECLARE demandCursor
     CURSOR FOR
     SELECT p.id,
            p.T2P,
            p.T3P,
            p.T4P,
            p.T5P,
            P.statusName
       FROM
       (
           SELECT dem.*, st.`name` as 'statusName'
           FROM `status` st
           INNER JOIN `events` eve
               ON eve.id_status = st.id
           INNER JOIN `demand` dem
               ON eve.id_demand = dem.id
           WHERE st.`name` IN ('A', 'B', 'C', 'D')
               AND eve.id IN
               (
                   SELECT MAX(even.id) ev
                   FROM `demand` de
                   INNER JOIN `events` even
                       ON even.id_demand = de.id
                   GROUP BY de.id
               )
       ) AS p;

    -- a cursor that runs out of data throws an exception; we need to catch this.
    -- when the NOT FOUND condition fires, "done" -- which defaults to FALSE -- will be set to true,
    -- and since this is a CONTINUE handler, execution continues with the next statement.   

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    DROP TEMPORARY TABLE IF EXISTS days_off;
    CREATE TEMPORARY TABLE IF NOT EXISTS days_off
    (
        date_off VARCHAR(5)
    );

    INSERT INTO days_off VALUES('01-01'),
                               ('05-01'),
                               ('05-08'),
                               ('07-14'),
                               ('08-15'),
                               ('11-01'),
                               ('11-11'),
                               ('12-25');

    -- open the cursor

    OPEN demandCursor;

    my_loop: -- loops have to have an arbitrary label; it's used to leave the loop
    LOOP

      -- read the values from the next row that is available in the cursor

      FETCH demandCursor INTO p_id, pT2P, pT3P, pT4P, pT5P, pstatusName;

      IF done THEN -- this will be true when we are out of rows to read, so we go to the statement after END LOOP.
        LEAVE my_loop;
      ELSE
        CASE pstatusName
            WHEN 'A' THEN
                SET pT2P=get_next_valid_date(pT2P);
                SET pT3P=get_next_valid_date(pT3P);
                SET pT4P=get_next_valid_date(pT4P);
                SET pT5P=get_next_valid_date(pT5P);

            WHEN 'B' THEN
                SET pT3P=get_next_valid_date(pT3P);
                SET pT4P=get_next_valid_date(pT4P);
                SET pT5P=get_next_valid_date(pT5P);

            WHEN 'C' THEN
                SET pT3P=get_next_valid_date(pT3P);
                SET pT4P=get_next_valid_date(pT4P);
                SET pT5P=get_next_valid_date(pT5P);

            WHEN 'D' THEN
                SET pT4P=get_next_valid_date(pT4P);
                SET pT5P=get_next_valid_date(pT5P);
        END CASE;
        UPDATE `demand`
        SET T2P=pT2P,
            T3P=pT3P,
            T4P=pT4P,
            T5P=pT5P
        WHERE id=p_id;
      END IF;
    END LOOP;
    CLOSE demandCursor;
    DROP TEMPORARY TABLE IF EXISTS days_off;
END$$