SQL ROW_NUMBER() function and Audit tables

Comments (1) | Share

I had to search through an audit table to find times when the value changes in a specific field. Considering an audit table

DateModified DateTime

ID varchar()

Quantity decimal
Price decimal

I wanted to find the rows when Quantity changed for the same ID.

I've been able to accomplish that using the ROW_NUMBER function of SQL 2005

Here's the query:

select Row_Number() OVER (PARTITION BY ID Order BY  ID, DateModified) as RowID, DateModified, Quantity, ID
INTO #TempData
from AuditTable

select a.ID, b.DateModified, a.Quantity as FromValue, b.Quantity as ToValue from #TempData a
inner join 
#TempData b
on a.ID = b.ID
and a.RowID = b.RowID-1
and a.Quantity != b.Quantity 
order by 1, 2

drop table #TempData

Cross Posted from http://blog.tfanshteyn.com/2008/04/sql-rownumber-function-and-audit-tables.html