MySQL query using date comparison

romic

Hi to ALL!

Please help me with the following question. I have a query to the DB that shows me the number of inquiries per property in the last year (see query below).

SELECT r.IDProperty, COUNT( * ) AS num, p.IDProperty, p.st_date, p.IDOwner, p.RegDate, u.IDUser, u.Email, r.RegDate
FROM reservation AS r, property AS p, users AS u
WHERE r.IDProperty = p.IDProperty
AND DATE_SUB( CURDATE( ) , INTERVAL 1 Year ) <= r.RegDate
AND p.IDOwner = u.IDUser
GROUP BY r.IDProperty
ORDER BY 2 DESC

Now my question is: How should I modify the existing query so that it would show the result according to one rule.
and the rule is: The query should show the results for the period starting from the property registration(day and month) BUT not more than one year ago from current date.

I mean that, if the property was registered at 21 march 2004 the result for that property should show the number of inquiries registered starting from 21 march 2006 until now
if the property was registered at 25 august 2004 the result for that property should show the number of inquiries registered from 25 august 2005 until now and so on

Thanks a lot for your help or for any suggestions you might have

liquorvicar

How about adding an extra field to record the last time the registration was renewed, i,e

id -- date_registered -- date_renewed
1 -- 2004-21-03 -- 2006-21-03
2 -- 2004-08-25 -- 2005-08-25
then write a cronjob to add a year to the renewed_date where the renewed date is a year ago.

romic

Thanks for the suggestion.
To tell you the truth I thought about this also but this is not the solution I was looking for. 🙁