if most recent effec_dt < current date in event, then select ... ? 1

[xq]

i am try to select effect_dt which is in the future from eventsi table where eventsi.comments = 'rate', but before i do that, i have to check if the mose recent effect_dt is in the past in the event table, if so i have to select the effect_dt from eventsi table where eventsi.comments= 'principal', will i be able to do that in sql? i've written the code as beblow, but select nothing. really appreciate if anyone would help me out. Thanks a lot!

select deals.ID, eventsi.effect_dt

FROM deals, event, eventsi

WHERE deals.ID= eventsi.ID AND
effecti.effect_dt =
(SELECT MIN(eventsi.effect_dt)
FROM eventsi
WHERE (
(eventsi.effect-dt > = CURRENT DATE)AND
(eventsi.ID = deals.ID) AND
(eventsi.comments = 'RATE') OR
((SELECT MAX(event.effect_dt)
FROM event
WHERE event.commencts = 'RATE') <
CURRENT DATE
AND eventsi.comments = 'PRICIPAL'
AND eventsi.ID = event.ID
)
)
)

 

[angiole]

I think I understand your problem, and I have a solution, but could you list which table has which columns before we start?

AA

 

[xq]

thanks a lot here's the table:
Deals Event Eventi
ID ID effect_dt Comments ID effect_dt comments
1 1 1/Jun rate 1 2/Jun rate
2 1 1/Aug rate 1 2/Aug rate
1 1/Sep prin 1 2/Sep prin
1 1/Oct rate 2 2/May rate
3 2 1/Mar Prin 2 2/Apr prin
4 2 1/Jun rate 2 2/Aug prin
5 2 1/Jul rate 3 2/Jul rate
6 3 1/Apr rate 3 2/Sep prin
4 1/May rate 3 2/Oct prin
4 1/Jun rate 4 2/May rate
4 1/Oct prin 4 2/Jul rate
5 1/Jun rate 4 2/Aug prin
5 1/Jul rate 5 2/Jul rate
5 1/Sep rate 5 2/Sep rate


firt i have to check if the the deals last effect_dt where comment is rate is in the past in the event table, and current date is 10 july.(i didn't make myself very clear at first time sorry.). so i have Deals ID 2 ,3 ,and 4 . the i have to select effect_dt for those deals from eventsi wher e comments = 'prin' and effect_dt in eventsi is in the nearest future, and for rest ID i have to look where comments is 'rate' and effect_dt is in the nearest future as well, so the ouput should be:

ID effect_dt
1 2/Aug
2 2/Aug
3 2/Sep
4 2/Aug
5 2/Sep

thanks a lot!

 

[angiole]

XQ,

I recreated your db tables to make sure the results are what you're expecting, and according to your expected result, you only need to read the EVENTI table with the following query:

SELECT ID, MIN(EFF_DT) AS MaxOfEFF_DT
FROM EVENTI
WHERE EFF_DT > #2002-07-10#
GROUP BY ID;

to give the result

ID MaxOfEFF_DT
1 2002-08-02
2 2002-08-02
3 2002-09-02
4 2002-08-02
5 2002-09-02

Hope this helps

AA