6.6 Generating Time-Restricted Snapshots

6.6.1 Problem

You want to generate a snapshot of a table with the rows as they appeared at a specific point in time in the past.

6.6.2 Solution

A time-restricted snapshot is one that returns a view of the table as of a given moment in time. The StockLog audit log can be used to build a time-restricted snapshot of the Stock table.

This solution builds on the previous recipe for building a snapshot of the table as it currently exists. The difference is that we are now restricting the results based on the timestamp in the EventTime column. The @TIME variable in this recipe represents the point in time that you want the snapshot to represent.

SELECT ProductID, Qty
FROM StockLog
WHERE logID IN 
   (SELECT 
      (SELECT (CASE WHEN MAX(Type)!='D' 
         THEN MAX(s.LogID) 
         ELSE 0 END) 
      FROM StockLog s1 
      WHERE s.ProductID=s1.ProductID 
         AND s1.LogID=MAX(s.LogID))
   FROM StockLog s
   WHERE s.EventTime <= @TIME
   GROUP BY ProductID)

6.6.3 Discussion

As you can see, this code is an extension of the current snapshot solution shown previously. That solution has been extended to include one additional restriction in the WHERE clause that limits the results to the most current data as of the date represented by the @TIME variable. Log records generated after the time in question are ignored. The resulting snapshot represents the data in the Stock table at the exact moment specified by @TIME.

You need to set the @TIME variable to a valid date and time prior to executing this query. Alternatively, you can replace @TIME with a constant representing a valid date/time value.

The query in this recipe uses s.EventTime <= @TIME to capture all events up to and including the time in question. If you only want to consider events that occurred prior to the specified time, use the less than (<) operator.