Re: returning the closest date
Ack, I made some ugly typos there didn't I? My apologies. As for the "train wreck" comment, the design just looks like you're asking for erroneous data, and the intent of the purchaset table is not especially clear. I the stock table related to it by the pid, but that could be wrong - either way, if you want your table structure to be _crystal_ clear, simply post your create table statements along with your question ;)
Anyway I corrected my typos. This should give you what you are asking for.
SELECT p1.pid, c1.colour,
(SELECT p2.seadate FROM purchaseset AS p2 WHERE p2.seadate > p1.seadate AND p2.pending = 1 AND NOT EXISTS(SELECT * FROM purchaseset AS p3 WHERE p3.seadate BETWEEN p1.seadate AND p2.seadate),
(SELECT p4.airdate FROM purchaseset AS p4 WHERE p4.seadate > p1.seadate AND p4.pending = 1 AND NOT EXISTS(SELECT * FROM purchaseset AS p5 WHERE p5.airdate BETWEEN p1.airdate AND p4.airdate)
FROM purchaset AS p1
INNER JOIN stock AS s1
ON s1.pid = p1.pid
INNER JOIN prodcolour AS c1
ON c1.colourid = s1.colourid
WHERE p1.pending=1
AND p1.airdate >=0
AND p1.seadate >=0
Subject
Written By
Posted
Re: returning the closest date
April 07, 2005 09:30PM
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.