help modifying the following query

[mbarandao]

hello:

I'm trying to modify the following query so that it only select rows where

90 - DATEDIFF(CURDATE(),date) as days_remaining

is greater than negative 15. (days_remaining > -15)

$query="select servicearea, date, customerid, clientID, 90 - DATEDIFF(CURDATE(),date) as days_remaining from servicesrendered where servicearea=' Oil Change ' and DATEDIFF(CURDATE(),date) > 79 group by clientID order by id desc";

To put the query into perspective, essentially, if my date is 2011-01-15 and my $limit value is 90 days, the code performs a search in the table for records which have a servicearea value of " Oil Change " and using the date and the $limit to thus determines how many days are left --as of today, until those 90 days expire. In some cases, the query will return negative numbers where the target date has come and gone. It is here that want to create limit. After the -15 days, I want the query above to ignore any records with this number of days expired.

I hope this makes sense.

Any thoughts!
Mossa

[ddymacek]

There are a couple of ways to get the result you want. first you can alter your query, I'm not sure if you want to cut off all negative numbers, before your 'group by' statement and after (79) you should be able to add:
and 90 - DATEDIFF(CURDATE(),date) > 0 group by ...
which would only return you items that do not have a negative return value. > -15 would be if you want some of the negative values but nothing > -15 returned.

The second way to approach your problem is to pull the query as is and sort the data on the other side. Assuming you are putting your result set into some type of an array, you could always check the 'days_remaining' field to see if it is positive and only add those items into your return array.

[mbarandao]

There are a couple of ways to get the result you want. first you can alter your query, I'm not sure if you want to cut off all negative numbers, before your 'group by' statement and after (79) you should be able to add:
and 90 - DATEDIFF(CURDATE(),date) > 0 group by ...
which would only return you items that do not have a negative return value. > -15 would be if you want some of the negative values but nothing > -15 returned.

The second way to approach your problem is to pull the query as is and sort the data on the other side. Assuming you are putting your result set into some type of an array, you could always check the 'days_remaining' field to see if it is positive and only add those items into your return array.

Thank you very much for the response and the fix to my request. I'm happy to say that the solution --the first option, was indeed correct. It performed as expected.

here is the final sql:

$query="select servicearea, date, customerid, clientID, 90 - DATEDIFF(CURDATE(),date) as days_remaining from servicesrendered where servicearea=' Oil Change ' and DATEDIFF(CURDATE(),date) > 79 and 90 - DATEDIFF(CURDATE(),date)>-15 group by clientID order by id desc";

Again, thank you!
Mossa