returning the closest date
Posted by:
t c
Date: April 06, 2005 09:23PM
MySQL version 4.1.
---------------
Table layout:
---------------
purchaset (recording the product purchasing information):
pid | pending | airdate | seadate
PK (auto increment) | tinyint(1), default=0, used as a boolean, if product is on it's way, the value is 1, if product is here and booked into the stock, then it's set as 0 | estimated airfreight delivery date as a timestamp yyyy-mm-dd | ditto for seadate, but a seafreight date.
prodcolour lists product colours:
colourid | colour
PK (auto increment) | varchar of the product colours
stock:
Is where all the main purchasing,returns and sales info is held for each model and colour.
------------
Required:
------------
To retrieve, amongst other fields, the next available airdate and seadate for each product where the purchase has not been received yet (ie pending =1). This is so I can let users know when the product will be back in stock. I am looking for a way to find the next date closests to now(). The best I"ve been able to to so far is the following.
-------------
Query:
-------------
select colourid, colour, min(datediff(airdate,now())) as airdate, min(datediff(seadate,now())) as seadate
from purchaset, prodcolour
where purchaset.pending=1
and airdate >=0
and seadate >=0
and colourid in (select colourid from stock where pid in (select pid from purchaset where pending = 1) group by colourid) group by colourid
This finds the difference in days from the current date and then finds the minimum date difference for each colour id (group by colourid) for the airdate and seadates. If for some reason the product is delayed and the final delivery takes place after the estimated airdate or sedate, the above airdates will be negative numbers, so I have excluded those in the where clause.
Is there a better way to do this that will produce the actual closest date without changing the format of the date from which it is stored in the db? i am concerned at the time it takes to have to convert the datediff back into a usable date for my application.
Also, I do not know if there are any problems with the query above. It seems to work OK so far, but the further processing needed to format the dates is a pain and is slow.
Any help would be great, tks.