Label Cloud

Wednesday, April 09, 2008

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


Share/Save/Bookmark

No comments:

Directory of Computers/Tech Blogs