如何在MySQL存储过程中计算这些日期差和乘法? [英] How can I calculate these date difference,multiplication in a MySQL stored procedure?
问题描述
我正在尝试在mysql中实现一个过程，该过程基于计算返回天数.输入购买日期(added_date)，然后完成计算.应该相对简单一些，但我似乎无法使它正常工作.我不确定这是否适合选择语句"，如果这非常简单，这是我第一次在程序中运行，我对此表示歉意.
I am trying to implement a procedure within mysql that returns the number of days based on a calculation. The purchase date (added_date) is passed in and then the calculation is done. Should be relatively straight forward but I cannot seem to get it to work. I am not sure if this is an appropriate situation for a "select statement" and I apologize if this is extremely simple but this is my first run at procedures.
如果有人可以告诉我该怎么办，将不胜感激.
If somebody could please tell me what should i do so, it would be much appreciated.
谢谢！
我有一个名为"计算"的表(表3)，该表包含字段"elap_yend"(整数)，该字段在调用存储过程之前一直为空.
I have a table called "calculation"(TABLE 3) that contains the field "elap_yend " (int) which is currently empty until the stored procedure is called.
表1:
cid parent_cat category_name category_life
22 0 Office Equipment-M' 1080
23 0 Office Equipment-O' 1800
24 0 F & F' 3600
25 0 Staff assets', 1800
27 0 Motor vehicle', 2880
28 0 Air conditioner' 5400
29 0 Land & Building', 2160
30 0 Temporary Partition 365
31 0 Electrical Fittings 3600
32 0 Generator' 5400
33 0 Software' 1800
34 0 Computer-N' 2160
35 24 chair' 3600
Tble 2:
pid cid product_name product_price added_date cgst sgst igst total depre
60 22 RHFL\test\001 20000 2018-11-02 1800 1800 0 23600 o
61 27 RHFL\test\002 13500 2018-11-02 2345 2345 0 15930 12
62 29 RHFL\test\003 65000 2018-11-02 2345 2345 0 76700 12
63 31 RHFL\test\004 10000 2018-11-02 2345 2345 0 1180 12
64 24 RHFL\test\005 10000 2018-11-02 2345 2345 0 11800 1
65 24 RHFL\test\006 13500 2018-11-02 2345 2345 0 15930 12
66 34 RHFL\test\007 13500 2018-11-02 2345 2345 0 15930 12
67 22 RHFL\test\008 65004 2018-11-02 2345 2345 0 76704 12
68 25 RHFL\test\009 10000 0000-00-00 2345 2345 0 11800 12
69 22 RHFL\test\010 65000 0000-00-00 2345 2345 0 76700 10
70
表3:存储过程的输出应反映在下表中:
Table 3: output of the stored procedure should reflect in the below table:
end_date elap_yend rem_days depre_cur cur_wdv depre_next next_wdv acc_depre
2018-11-02 0 0 0 0 0 0
输出计算:
上面是输出为零的样本.这不是确切的输出，但是每当我调用存储过程时，都应该在计算以下进行这些操作.
Above is the sample output with zero. It is not the exact output but whenever i am calling the stored procedure it should do these below calculation.
DELIMITER $$
CREATE PROCEDURE calculationTemp(
in till_yend date,
)
BEGIN
DECLARE till_yend date;
SELECT datediff(now(),added_date) INTO till_yend
FROM products
IF till_yend > 0 THEN
SET till_yend = select datediff(now(),added_date) from products;
ELSEIF till_yend < 0 THEN
SET till_yend = 0;
END IF;
insert into calculationTemp
END$$
计算方式:
过去的年末: 直到每年31/March/xxxx为止elapsed_yend = add_date(来自产品表(No:2)).
Elapsede year end : elapsed_yend = added_date(from products table (No:2)) till every year 31/March/xxxx.
这两个日期之间的天数
remaining_days = category_life-(addd_date(从产品(No:1))到日期之间的日期差)
remaining_days = category_life - (date difference between added_date(from products (No:1)) till date)
category_life(来自类别表(编号:1))例如:移动设备的使用寿命为1080天.
category_life(from categories table (No:1))Ex:mobile it's life will be 1080 days.
当年折旧: depreciation_cur =(depre/category_life)* elapsed_yend
Current Year depreciation: depreciation_cur = (depre/category_life)*elapsed_yend
-
当年的书面价值:
Current Year Written down value:
current_wdv = depre-depreciation_cur
current_wdv = depre - depreciation_cur
下一年的折旧: depreciation_next =(depre/category_life)* D D =每年01/April/xxxx到end_date之间的天差(来自计算表)
Next Year depreciation : depreciation_next = (depre / category_life) * D D = days difference between every year 01/April/xxxx till end_date(from calculation table)
下一年的书面价值:
next_wdv = current_wdv-depreciation_next
next_wdv = current_wdv - depreciation_next
累计折旧:
accumulate_depre =折旧_cur +折旧_pext
accumulate_depre = depreciation_cur + depreciation_next
推荐答案
我的答案在这里发布，以获取有关改善我的商店流程的任何想法.谢谢！！！
Got My answer posting here to get any idea regarding to improve my store procedure. Thank You!!!
BEGIN
DECLARE cur_depre,cur_wtvalue,next_depre,next_wtvalue,accum_depre,pro_loss double(20,2);
DECLARE days,next_diff int;
CREATE TABLE IF NOT EXISTS calc AS (SELECT p.pid,p_date,days,next_diff,cur_depre,cur_wtvalue,next_depre,next_wtvalue,accum_depre,pro_loss,c.cid,p.added_date,c.category_life,p.depre,p.sale_status,p.sale_date,p.sale_amount from products p,products d,categories c WHERE p.pid=d.pid AND p.cid=c.cid
);
INSERT INTO calc (PID)
SELECT PID FROM products WHERE PID NOT IN (SELECT PID FROM calc);
UPDATE calc set days = datediff(p_date,added_date);
UPDATE calc set days = 0 WHERE datediff(p_date,added_date) < 0;
UPDATE calc set next_diff = datediff(sale_date,DATE_ADD(p_date, INTERVAL 1 DAY) );
UPDATE calc set next_diff = datediff('2019-03-31',added_date) WHERE added_date>p_date;
UPDATE calc set cur_depre = (depre/category_life)*datediff(p_date,added_date);
UPDATE calc set cur_depre = 0 where (depre/category_life)*datediff(p_date,added_date)<0;
UPDATE calc set cur_wtvalue =(depre-(depre/category_life)*datediff(p_date,added_date));
UPDATE calc set cur_wtvalue = 0 WHERE added_date>p_date;
UPDATE calc set next_depre =( (depre/category_life)*datediff(sale_date,DATE_ADD(p_date, INTERVAL 1 DAY) )) where added_date < p_date;
UPDATE calc set next_depre =( (depre/category_life) * datediff('2019-03-31',added_date)) WHERE added_Date > p_date;
UPDATE calc SET next_wtvalue = depre -(depre/category_life)*datediff(p_date,added_date) - (( (depre/category_life)*datediff(sale_date,DATE_ADD(p_date, INTERVAL 1 DAY) )) );
UPDATE calc SET next_wtvalue = depre - (( (depre/category_life) * datediff('2019-03-31',added_date))) WHERE added_date>p_date;
UPDATE calc SET accum_depre = ((depre/category_life)*datediff(p_date,added_date))+( (depre/category_life)*datediff(sale_date,DATE_ADD(p_date, INTERVAL 1 DAY) )) ;
UPDATE calc SET accum_depre =( (depre/category_life) * datediff('2019-03-31',added_date)) WHERE added_Date > p_date;
UPDATE calc SET pro_loss = sale_amount-(depre -(depre/category_life)*datediff(p_date,added_date) - (( (depre/category_life)*datediff(sale_date,DATE_ADD(p_date, INTERVAL 1 DAY) )) ) );
UPDATE calc SET pro_loss = 0 WHERE sale_status=0;
SELECT * from calc;
END
这篇关于如何在MySQL存储过程中计算这些日期差和乘法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
