MySQL Forums
Forum List  »  Newbie

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.

Options: ReplyQuote


Subject
Written By
Posted
returning the closest date
t c
April 06, 2005 09:23PM
t c
April 07, 2005 02:30PM
t c
April 07, 2005 11:05PM
t c
April 07, 2005 04:28PM
t c
April 07, 2005 04:29PM
t c
April 08, 2005 04:23PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.