>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