CODEWITHSUNDEEP
mysqlsql
user682417
user682417

Reputation: 1518

query to get the monthly amount using mysql

membertomships table

member table

Can I get the monthly amount that the member is paying, like this table:

member_id   monthly amount
----------------------------
1           30.00
2           40.00
3           50.00                  
4           10.00

I have tried this but it was giving the total amount up to the mentioned date.

TIMESTAMPDIFF (MONTH, membership_startdate, '2011-05-10') * `membership total amount` As AMOUNTGIVENDATE ,

Now I want the money per month.

Upvotes: 1

Views: 498

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74267

You're asking to compute amount per arbitrary unit of time, right? How do you compute that (should be pretty easy to figure out)?

Here's a clue: you already have the dividend. And you already have a representation of a period of time (the start/end dates). You need to figure out the divisor. How might you do that?

Upvotes: 0

pjp
pjp

Reputation: 17629

  1. Find an SQL function tell you the number of months between the start date and the end date
  2. Take the total value and divide it by the number of months

Roughly it might look something like this...

     SELECT 
         member_id, 
         30*membership_total_amount/DateDiff(membership_enddate, membership_startdate) As monthly_amount
     FROM
         member

You will need to see what date functions mysql supports. See the mysql documentation

Upvotes: 1

Related Questions