sql - mysql Event running yearly calculations

Question

I have the following code

CREATE EVENT myevent2
ON SCHEDULE EVERY '1' YEAR
STARTS commit_date + INTERVAL 1 YEAR
DO
UPDATE lms.loan 



        if new.app_loan_type='Computer Loan' then 
    SET NEW.app_yearly_intrst = (NEW.app_ln_amnt *(NEW.computer_interest/100));
    end if;


    if new.app_loan_type="Miscellaneous Loan" then 
    SET NEW.app_yearly_intrst = (NEW.app_ln_amnt *(NEW.miscellaneous_interest/100));
    end if;

    if new.app_loan_type="Motor Vehicle Loan" then 
    SET NEW.app_yearly_intrst = (NEW.app_ln_amnt *(NEW.motor_vehicle_interest/100));
    end if;

    if new.app_loan_type="Motor Vehicle Insurance Loan" then 
    SET NEW.app_yearly_intrst = (NEW.app_ln_amnt *(NEW.mv_insurance_interest/100));
    end if;

    if new.app_loan_type="Motor Vehicle Repair Loan" then 
    SET NEW.app_yearly_intrst = (NEW.app_ln_amnt *(NEW.mv_repair_interest/100));
    end if;


    if new.app_loan_type="Salary Advance Loan" then 
    SET NEW.app_yearly_intrst = (NEW.app_ln_amnt *(NEW.salary_advance_interest/100));
    end if;


    if new.app_loan_type="Tertiary Loan" then 
    SET NEW.app_yearly_intrst = (NEW.app_ln_amnt *(NEW.tertiary_interest/100));
    end if;

    SET NEW.app_mnthly_intrest = (NEW.app_yearly_intrst/12);
    SET NEW.app_quarterly_intrest = (NEW.app_mnthly_intrest * 3);
    SET NEW.app_amnt_owed = (NEW.app_ln_amnt+ NEW.app_yearly_intrst);

  END$$

I am trying to create an event to calculate interest for two loan types: 'Computer Loan' and 'Miscellaneous Loan'.. etc, on a Yearly basis, however the yearly basis is dependent on the the commit_date column that would be a year from the commit_date column, then if the app_loan_type is 'Computer Loan'or 'miscellaneous Loan' then the yearly interest is calculated Im also calculating monthly interest, Quarterly Interest and total amount owed, but my main problem is how to set the interval of commit_date and current date to be 1 year

Answer 1

i am not sure if you can use a column value in the STARTS clause, but there is a workaround for this you can schedule this event for daily and have a extra check in all the IF clause you have, to check the date difference between commit_date and current_date() is one year.

something like:

if new.app_loan_type="Salary Advance Loan" and DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), commit_date)), "%Y")+0 = 1 then