mysql - trying to get the number of months

Question

membership table

• membership_startdate (2011-01-12)
• membership_dueday values like only dates (09,08,07)
• member_id

member table

• member_id

How can I get count of the number of months that the member has paid till now and taking into consideration (membership_dueday)? Suppose if membership_startdate is 2011-01-01 and membership_dueday is 15, the the number of months count up to till now is 5.5

I have tried this code

SELECT COUNT(NUMBEROFMONTHS) 
  FROM membership 
 WHERE NUMBEROFMONTHS = PERIOD_DIFF(membership.membership_startdate, CURDATE());

It was giving error like this:

Error Code: 1054
Unknown column 'NUMBEROFMONTHS' in 'field list'

...but it does not taking into account the membership_dueday...

Answer 1

The 1054 error is because the column does not exist in the table(s) defined in the FROM clause. Additionally, the WHERE clause is not used to set a variable, or column alias -- it's for filtering rows returned.

Use DATEDIFF:

SELECT t.member_id,
       DATEDIFF(LEAST(NOW(), t.membership_dueday), t.membership_startdate) / 30
  FROM MEMBERSHIP t

The LEAST function will return the lowest of the two dates, so it will use the current date if the due date is in the future. You can use the GREATEST function if you want that reversed.