I had to search through an audit table to find times when the value changes in a specific field. Considering an audit table
I wanted to find the rows when Quantity changed for the same ID.
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