SQL ROW_NUMBER() function and Audit tables
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
Technorati Tags: SQL Server, Tips
