mysql事件运行年度计算 [英] mysql Event running yearly calculations

问题描述

我有以下代码

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$$

我正在尝试创建一个事件来计算兴趣对于两种贷款类型：计算机贷款和杂项贷款等，但按年度计算，但年度依赖于将来自commit_date列的一年的commit_date列，如果app_loan_type为计算机贷款杂项贷款则计算年利息Im也计算每月利息，季度利息和欠款总额，但我的主要问题是如何将commit_date和当前日期的间隔设置为1年

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

推荐答案

我不认为您可以使用动态值为 timestamp 创建事件语法。而是每天运行你的事件，你可以使用事件中更新查询的where子句中的 datediff 函数来检查哪些数据应该被更新。你也可以检查这样的确切日期： where day（now（））= day（commit_date）and month（now（））= month（commit_date）and year（now（））= year commit_date）+ 1; ，但是您不会在闰眼的情况下更新，因此您应该使用以下内容：

I do not think you can use a dynamic value for the timestamp used in the create event syntax. Instead run your event everyday, and you can use the datediff function in the where clause of the update query in the event to check which data should be updated. You could also check the exact date like this: where day(now()) = day(commit_date) and month(now()) = month(commit_date) and year(now()) = year(commit_date) + 1;, but you wouldn't update in the case of leap eyar, so you should use something like this:

delimiter ||

CREATE EVENT myevent2
ON SCHEDULE EVERY 1 DAY
STARTS now()
DO
    UPDATE lms.loan SET app_yearly_intrst = (app_ln_amnt *(computer_interest/100)) where app_loan_type = 'Computer Loan' and datediff(date(now()),commit_date) >= 365;
    UPDATE lms.loan SET app_yearly_intrst = (app_ln_amnt *(miscellaneous_interest/100)) where app_loan_type = 'Miscellaneous Loan' and datediff(date(now()),commit_date) >= 365;
    UPDATE lms.loan SET app_yearly_intrst = (app_ln_amnt *(motor_vehicle_interest/100)) where app_loan_type = 'Motor Vehicle Loan' and datediff(date(now()),commit_date) >= 365;
    UPDATE lms.loan SET app_yearly_intrst = (app_ln_amnt *(mv_insurance_interest/100)) where app_loan_type = 'Motor Vehicle Insurance Loan' and datediff(date(now()),commit_date) >= 365;
    UPDATE lms.loan SET app_yearly_intrst = (app_ln_amnt *(mv_repair_interest/100)) where app_loan_type = 'Motor Vehicle Repair Loan' and datediff(date(now()),commit_date) >= 365;
    UPDATE lms.loan SET app_yearly_intrst = (app_ln_amnt *(salary_advance_interest/100)) where app_loan_type = 'Salary Advance Loan' and datediff(date(now()),commit_date) >= 365;
    UPDATE lms.loan SET app_yearly_intrst = (app_ln_amnt *(tertiary_interest/100)) where app_loan_type = 'Tertiary Loan' and datediff(date(now()),commit_date) >= 365;
    UPDATE lms.loan SET app_mnthly_intrest = (app_yearly_intrst/12), app_quarterly_intrest = (NEW.app_mnthly_intrest * 3), app_amnt_owed = (app_ln_amnt+ app_yearly_intrst) where datediff(date(now()),commit_date) >= 365;
END||

这篇关于mysql事件运行年度计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！