6.6 Generating Time-Restricted Snapshots6.6.1 ProblemYou 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 SolutionA 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 DiscussionAs 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.
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. |