mysql - 存储过程计算应纳税额

我一直在计算一个员工的年应纳税额，而不是不断变化的工资率。

salary_assigned_date | salary
-------------------------------
    2011-12-06          5000
    2012-01-05          10000
    2012-02-10          15000
    2012-04-08          20000
    2012-08-01          28000

现在，我2012年的应纳税月数应如下：
我假设一个月的天数是30天。

month   |   taxable_amount
-----------------------------------------------
  01            833.33 + 8333.33   /* Since salary has been changed
                                      at 6th of month,
                                      for 5 days,
                                      taxable amount = 5000/30*5
                                      => 833.33
                                      and for remaining 25 days
                                      = 10000/30*25=> 8333.33
                                      and same case for remaining months.*/
  02            3000 + 10500
  03            15000
  04            4666.67 + 15333.33
  05            20000
  06            20000
  07            20000
  08            933.33 + 27066.67
  09            28000
  10            28000
  11            28000
  12            28000

我试图编写一个存储过程来计算应纳税额，但我无法完成。
有人能帮忙吗？

最佳答案

您需要一个SQL语句，将表中的记录与表中具有下一个薪资值的记录连接起来…您还需要使用CTE（或任何**AA**）来生成所有没有工资变动的月份。*[感谢@neville的评论]
请原谅SQL Server语法，我不会为您查找MySQL等价物…目的应该是明确的。我知道MySQL有自己的函数，相当于SQL服务器的日期函数getdate()、DateDiff()、DateAdd()和Day()。

 With Dates(dt) As
 ( Select min(salary_assigned_date)
   From yourTable
   Union All
   Select DateAdd(month,1, dt)
   from dates
   where dt < getdate())  -- replace getdate() with parameter for max date to calculate

  -- If MySQL has no equivalent to CTE, you need to generate a temp table with
  -- these dates in it and use that instead of the [Dates] construction

   Select t.dt, t.salary/30.0 * (day(t.dt)-1) +
        + n.salary/30.0 * (31 - day(t.dt))
   From Dates d
      join yourTable t On t.salary_assigned_date =
                    (Select Min(salary_assigned_date)
                     From test where salary_assigned_date >= d.dt)
      join yourTable n On n.salary_assigned_date =
                    (Select Min(salary_assigned_date)
                      From test where salary_assigned_date > d.dt)

   Select t.salary/30.0 * (day(t.salary_assigned_date)-1) +
        + n.salary/30.0 * (31 - day(t.salary_assigned_date))
   From table t
       join table n On n.salary_assigned_date =
                    (Select Min(salary_assigned_date) From table
                     Where salary_assigned_date > t.salary_assigned_date)

关于mysql - 存储过程计算应纳税额，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15549936/