Using SQL to retrieve previous row data

Question

I have a requirement when I need to get data from the previous row for use in the calculation to give the status to the current row. This is a story table. The previous line will tell me if the data in the date field has changed.

I searched with cursors and this seems a bit complicated. Is this the best way to go?

I also tried to fix the value for the new field ...

newField = (Select field1 from table 1 where the previous row is) where I seem to be stuck. I cannot figure out how to select a line below the current line.

I am using SQL Server 2005

Thanks in advance.

Answer 1

-- Test data declare @T table (ProjectNumber int, DateChanged datetime, Value int) insert into @T select 1, '2001-01-01', 1 union all select 1, '2001-01-02', 1 union all select 1, '2001-01-03', 3 union all select 1, '2001-01-04', 3 union all select 1, '2001-01-05', 4 union all select 2, '2001-01-01', 1 union all select 2, '2001-01-02', 2 -- Get CurrentValue and PreviousValue with a Changed column ;with cte as ( select *, row_number() over(partition by ProjectNumber order by DateChanged) as rn from @T ) select C.ProjectNumber, C.Value as CurrentValue, P.Value as PreviousValue, case C.Value when P.Value then 0 else 1 end as Changed from cte as C inner join cte as P on C.ProjectNumber = P.ProjectNumber and C.rn = P.rn + 1 -- Count the number of changes per project ;with cte as ( select *, row_number() over(partition by ProjectNumber order by DateChanged) as rn from @T ) select C.ProjectNumber, sum(case C.Value when P.Value then 0 else 1 end) as ChangeCount from cte as C inner join cte as P on C.ProjectNumber = P.ProjectNumber and C.rn = P.rn + 1 group by C.ProjectNumber 

Answer 2

It really depends on what tells you that the line is the "previous line". however, a self-join should do what you want:

 select * from Table1 this join Table2 prev on this.incrementalID = prev.incrementalID+1 

Answer 3

If you have the following table

 CREATE TABLE MyTable ( Id INT NOT NULL, ChangeDate DATETIME NOT NULL, . . . ) 

The following query will return the previous record for any record from MyTable .

 SELECT tbl.Id, tbl.ChangeDate, hist.Id, hist.ChangeDate FROM MyTable tbl INNER JOIN MyTable hist ON hist.Id = tbl.Id AND hiost.ChangeDate = (SELECT MAX(ChangeDate) FROM MyTable sub WHERE sub.Id = tbl.Id AND sub.ChangeDate < tbl.ChangeDate)