Using SQL to get the previous rows data

I have a requirement where I need to get data from the previous row to use in a calculation to give a status to the current row. It's a history table. The previous row will let me know if a data has changed in a dat开发者_开发技巧e field.

I've looked up using cursors and it seems a little complicated. Is this the best way to go?

I've also tried to assgin a value to a new field...

newField =(Select field1 from Table1 where "previous row") previous row is where I seem to get stuck. I can't figure out how to select the row beneath the current row.

I'm using SQL Server 2005

Thanks in advance.

---

-- 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

---

This really depends on what tells you a row is a "Previous Row". however, a self join should do what you want:

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

---

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)