CODEWITHSUNDEEP
mysqlstored-procedures
Chris
Chris

Reputation: 545

MySQL: Stored procedure with variable DATE_ADD intervals

I'm trying to write a MySQL sproc that will copy certain table rows based on the value of one field, rwo_frequency. For example, if rwo_frequency = 2, then the date interval is one week, and my statement would look like this:

INSERT INTO table (start_date, <other columns>)
SELECT DATE_ADD(start_date, INTERVAL 7 DAY) AS start_date, <other columns>
FROM table
WHERE start_date = DATE_ADD(CURDATE(), INTERVAL -7 DAY)
AND rwo_frequency = 2;

So, if start_date for a record = '2021-06-04', then that record will be copied on 2021-06-11 and given that date as the start date.

Where I'm stuck is how to express rwo_frequency and the time interval for DATE_ADD as variables so that the sproc covers all values for rwo_frequency. So, if the frequency were monthly (rwo_frequency = 3), then the DATE_ADD interval would automatically be 'INTERVAL 1 MONTH'. How can I achieve this in the body of my sproc?

Upvotes: 2

Views: 1051

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562428

You can use a CASE expression to make the right side of that comparison a different value for each respective value of rwo_frequency.

INSERT INTO table (start_date, <other columns>)
SELECT DATE_ADD(start_date, INTERVAL 7 DAY) AS start_date, <other columns>
FROM table
WHERE start_date = CASE rwo_frequency
    WHEN 2 THEN DATE_ADD(CURDATE(), INTERVAL -7 DAY)
    WHEN 3 THEN DATE_ADD(CURDATE(), INTERVAL -1 MONTH) 
    ...
    END

If rwo_frequency does not match any of the cases, and you don't write an ELSE case, then result of the whole expressions is NULL.

Upvotes: 3

Related Questions