Geeks With Blogs
An educated guess (Timur Fanshteyn) Technology, Software Development and other ways to make an educated guess about the world...

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

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

Posted on Thursday, April 10, 2008 6:16 AM | Back to top


Comments on this post: SQL ROW_NUMBER() function and Audit tables

# re: SQL ROW_NUMBER() function and Audit tables
Requesting Gravatar...
very useful, i needed to find a request response pair in a query, there was no unique field in the response other than the fact that it was always the next row in the table. so i used a variation of the query above and it worked a treat, thanks to the poster.
Left by matt costello on Feb 02, 2009 10:25 AM

Your comment:
 (will show your gravatar)


Copyright © Timur Fanshteyn | Powered by: GeeksWithBlogs.net