>First join is incorrect if you have multiple previous records.
just ...
*-- To test a condition
INSERT INTO Agenda (iid , ddate , itraiterId , nstock ) VALUES ( 4 , vp_date -2 , 3 , 6)
INSERT INTO Agenda (iid , ddate , itraiterId , nstock ) VALUES ( 4 , vp_date -3 , 3 , 4)
INSERT INTO Agenda (iid , ddate , itraiterId , nstock ) VALUES ( 4 , vp_date -4 , 3 , 2)
I want the Only the value for vp_date -2 ( = the most recent day of vp_date )
>
>>Naomi,
>>
>>
>>You are right.
>>
>>
>> UPDATE ap ;
>> SET nstock = pd.nstock ;
>> FROM Agenda ap;
>> JOIN (;
>> SELECT ag.nstock, ag.iid FROM Agenda ag WHERE ag.ddate < (vp_date) ;
>> UNION ALL ;
>> SELECT T.nstock, dt1.iid ;
>> FROM ( ;
>> SELECT iid,itraiterId ;
>> FROM Agenda ag2 ;
>> WHERE ddate = (vp_date) ;
>> AND NOT EXISTS (SELECT * FROM Agenda ag3 ;
>> WHERE ag3.ddate < (vp_date) ;
>> AND ag3.iid = ag2.iid) ;
>> ) dt1 ;
>> JOIN Traiter T ON T.iid = dt1.itraiterId ;
>> ) pd ON ap.iid = pd.iid ;
>> WHERE ap.ddate = vp_date
>>
>>
>>Give a good result
>>
>>bernhart
>>