For a project I have been working on, I needed to tweak our data gathering process to start logging historical data so people in the future could go back and see what changes were made, when and by whom. In other words, someone in the future is going to have a really bad day. I thought the easiest way to accomplish this would be to create a new historical data table in the SQL Server database and just log a new row anytime an insert or update was recorded against the main table. A few stored procedure changes later, and that piece was up and running. Then came the challenging part. How to generate a report that translated these changes in a usable format.
Basically, we’d need to look back at that previous record and see what happened. I came at it a few different ways, but everything seemed to spit out incorrect data until I came across LAG. Evidently, LAG and LEAD have been around in the postgreSQL and mySQL world for many years and just got introduced to SQL Server in 2012. By using the PARTION BY and ORDER BY, you can effectively group and order your data to peek back at the value that came in the record before the current one. Here is how I have this working:
SELECT lch.Location_Category_History_ID, c.Category_Name, lch.Price, lch.Price - LAG(saph.Price) OVER (PARTITION BY lch.Location_Category_ID ORDER BY lch.Location_Category_ID) AS Price_Change, lc.Location_ID, lch.Modified_Date FROM Location_Category_History lch JOIN Location_Category lc ON lc_Location_Category_ID = lch.Location_Category_ID JOIN Category c ON lc.Category_ID = c.Category_ID WHERE lc.Location_ID = 555
Here is a sample result set that gets generated from this SQL query. Occassionally, you’ll notice that the price change on the initial record shows as NULL. This is due to there being no previous record to compare it with.