Fabio,
thank you very much. I like your SQL solutions.
I still need to learn ...
Agnes
>>Hi All,
>>
>>today I have little problem. I need an idea for an SQL SELECT.
>>
>>Given is a table like
>>
>>dDate, nValue
>>2008-1-1,1
>>2008-1-13,2
>>2008-1-11,5
>>2008-1-10,4
>>2008-1-2,3
>>
>>
>>the result should be
>>dDate, nValue(formula)
>>2008-1-1,1-nValue from 2008-1-2
>>2008-1-2,3-nValue from 2008-1-10
>>2008-1-10,4-nValue from 2008-1-11
>>2008-1-11,5-nValue from 2008-1-13
>>2008-1-13,2-0
>>
>>so the result should hold the date and the value of this day minus the value of the next ordered day.
>>Till now I do something like
>>
>>SELECT;
>> dDate,;
>> nValue,;
>> CAST(0 AS i) AS iId;
>> FROM curSource AS Cur1;
>> INTO CURSOR curOrdered READWRITE;
>> ORDER BY 1
>>
>>REPLACE;
>> iId WITH RECNO()
>>
>>SELECT;
>> Cur1.dDate,;
>> Cur1.nValue-NVL(cur2.nValue,0);
>> FROM curOrdered AS Cur1;
>> LEFT OUTER JOIN curOrdered AS cur2;
>> ON Cur1.iId+1=cur2.iId
>>
>>
>>How can I do this without the replace, oossibly in one SELECT?
>>
>>Agnes
>
>
>SELECT;
> dDate;
>, nValue - NVL((SELECT TOP 1 nValue FROM curSource WHERE dDate>X.dDate ORDER BY dDate),0)
> FROM curSource X;
> ORDER BY 1
>
Words are given to man to enable him to conceal his true feelings.
Charles Maurice de Talleyrand-Périgord
Weeks of programming can save you hours of planning.
OffThere is no place like [::1]