Stored procedure calculate taxable amount

Question

I have been working with calculating annual taxable amount of a staff over changing salary rates.

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

Now, my taxable amount for year 2012 in terms of months should be like this:

I have assumed no. of days in a month as 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

I tried to write a stored procedure in order to calculate the taxable amount but I could not accomplish this.

Can someone help on this ?

What's the formula for assignment on the first of the month? — Strawberry, Mar 21 '13 at 14:34
@AshReva : Its better not to post that code here, its messy and its not following english calendar. Its based on Nepalese calendar. — hsuk, Mar 22 '13 at 03:40
@Strawberry : Hmmm, there can not be general formula, but the concept is like what I mentioned for month 01. In order to calculate the annual taxable amount, if the salary has changed, from the very next day of the salary changed date, the new salary should be considered. — hsuk, Mar 22 '13 at 03:43

score 1 · Answer 1 · edited May 23 '17 at 11:49

you need a sql statement that joins a record in the table to the record in the table that has the next salary value... you also need to use a CTE (or whatever **MySQL equivalent* exists ) to generate all the months where no salary change occurs. * [Thanks to @Neville's comment]

Excuse the SQL server syntax, I am not going to look up the MySQL equivalents for you... the intent should be clear. I know MySQL has it's own functions equivalent to SQL servers' date functions getdate(), DateDiff(), DateAdd(), and 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)

It is actually more complicated than this. You have to add in months as well (there are fewer salary records than months in the year). — Gordon Linoff, Mar 21 '13 at 14:51
http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql — Neville Kuyt, Mar 21 '13 at 15:24

Stored procedure calculate taxable amount

1 Answers1