How to assign the previous row value to next row

Question

Date       Starting      Ending

10/01/2010      0            100
10/02/2010     100           200
10/03/2010     200           300
10/04/2010     300             0   

the table has only one column that is ending and i need the output similar to one shown above and using sqlserver 2005

Answer 1

May not be the best solution but following would work if its one time operation and performance is not a concern

CREATE FUNCTION GetPreviousVal
(
@PrevVal int ) RETURNS int AS BEGIN

DECLARE @ReturnVal INT

SELECT TOP 1 @ReturnVal = Ending 
FROM RowVal
WHERE Ending < @PrevVal
ORDER BY Ending DESC


RETURN @ReturnVal

END

GO

Now, use above function to update your data

UPDATE RowVal
SET Starting = ISNULL(dbo.GetPreviousVal(Ending),0)

Answer 2

Assuming a table called SampleData, and columns called Date and Starting, you can do this to query the table:

SELECT S1.Date, S1.Starting, ISNULL(S2.Starting, 0) as Ending
FROM SampleData S1
LEFT OUTER JOIN
(
    SELECT *
    FROM SampleData
) S2
ON DATEADD(d, 1, S1.Date) = S2.Date

Which will return:

Date       Starting      Ending

10/01/2010      0            100
10/02/2010     100           200
10/03/2010     200           300
10/04/2010     300             0 

If you want to update the table instead, you can do the following:

UPDATE SampleData
SET Starting = Ending
FROM SampleData S3 
INNER JOIN
(
    SELECT S1.Date, ISNULL(S2.Starting, 0) as Ending
    FROM SampleData S1
    LEFT OUTER JOIN
    (
        SELECT *
        FROM SampleData
    ) S2
    ON DATEADD(d, 1, S1.Date) = S2.Date
) S4 ON S3.Date = S4.Date