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

SQL ROW_NUMBER() function and Audit tables

ScriptDB

I was looking for a free utility to script database objects from command line. There aren't that many of them out. However, I did find one that was very good at getting the job done and came with full source code. ScriptDB from ElsaSoft.

I made a few changes to the code to make it easier to use the ScriptDB in the build scripts and accept more options from the command line. I was spoke with the original developer Jesse Hersch and he agreed to publish the new application to on an open source repository.

So now: Please check out the openly available ScriptDB on CodePlex at http://www.codeplex.com/ScriptDB. The application does almost everything I need from a command line tool, and now that it is easily available to the community, hopefully it will be improved farther.

ScriptDB

Using the SQL Server Hosting Toolkit

I've been developing SQL Server based project in the office and on my laptop. Synchronizing the database changes both schema and data is complicated and I was looking for good tool to help with it. Well, now there is one.

Microsoft released a 2nd CTP of SQL Server Hosting Toolkit. Its soul purpose is to create a script to recreate schema and data of a SQL serve database. All it takes is two clicks using the wizard.

Using the SQL Server Hosting Toolkit

Query ExPlus 2.01 beta is out

• Serious bug with saving list of connection strings is fixed.
• Settings are now saved using .NET 2.0 settings API
• help for command line arguments (-help)

Check it out at http://www.sourceforge.net/projects/queryexplus

Query ExPlus 2.01 beta is out

Query Express is now Query ExPlus on SourceForge

After downloading Query Express, I got in touch with the developer, Joseph Albahari, and asked him to put the project on SourseForge. A few more emails later regarding the Licensing (Source Forge does not support permissive free license), and a few days later, the Query ExPlus was born. Check it out at http://sourceforge.net/projects/queryexplus/

Query Express is now Query ExPlus on SourceForge