>Hi,
>I have records as below
>

>Name         Date         Action
>--------------------------------
>John         01/01/2005    Y
>John         01/03/2005    T
>John         01/04/2005    F
>
>I need to transform these records to :
>
>Name         Date         Date2        Action
>---------------------------------------------
>John         01/01/2005   28/02/2005     Y
>John         01/03/2005   31/03/2005     T
>John         01/04/2005   / /            F
>

>
>The date of next record will be the date2 of current record.
>
>Any way to do this in SINGLE SQL statement (VFP/SQL Server)?
>Currently I am using UDF() in SELECT list. Any other ways?
>
>Thank you

* MSSQL
UPDATE Table 
 SET Date2=DATEADD(d,-1,
     (SELECT MIN(X.Date) FROM table X WHERE X.date>Table.date and X.Name=Table.name))
 WHERE Table.date<>(SELECT MAX(X.Date) FROM table X WHERE X.Name=Table.name)

* vfp9 no supported

or

* MSSQL or VFP9
UPDATE Table SET Date2=Y.mindate-1;
FROM (SELECT Table.Name,Table.Date,MIN(X.Date) mindate FROM Table JOIN Table X ON X.date>Table.date and X.Name=Table.name;
GROUP BY Table.Name,Table.Date) Y WHERE Y.date=Table.date and Y.Name=Table.name