MySQL query based on SUM per month

The project is about a system where every X minutes of calls based on the given month its set a rate where mu开发者_高级运维ltiplied by these calls result in the commission to be paid.

Example: 1 - 1000 mins = * 0.01 resulting in a $10 pay if made 1000mins or the equivalent if lower. 1001 - 2000 mins = * 0.02 resulting in a $20 pay if made 2000mins ... 2000+ mins = * 0.03, so if made 2500 in calls, commission would be $75

Is it possible to set a query for such math? Need to take in consideration user will be setting different time frames to search, for example, from 1 to jan to 15 march.

In JAN, if it was made 1500mins, I will have to display a daily report multiplying per 0.02 In FEB, if it was made 4000min, I will .... per 0.03 In MAR, if it was made 500min till the date queried, I will ... 0.01

It also, needs to take in consideration, user might be searching for jan 1 to 15 and even if it results in only 500min, the whole month should be taken in consideration for the calc.

If you're using mysql, perhaps you can do something like this?

select 
     mon,
     case
         when total_time <= 1000
              then total_time * 0.01
         when total_time > 1000 
          and total_time <= 2000
              then total_time * 0.02
         when total_time > 2000
              then total_time * 0.03
     end case as pay
from
    (select
         sum(minutes) total_time,
         extract(month from date) mon
     from 
         calls
     group by 
         extract(month from date)
    )