General :: Ability To Report Historical Changes To Database

Selecting the "General" group as this involves SQL Server Stored Procedures (SP) and VBA code and Reports and and and...

Client has requested exception type reporting noting when a price in a Bill of Materials (BOM) changes.

I am thinking to solve this with the following steps:

1) EXEC SP to run "this week's" BOM reports, automated, figure out how to print to PDF or something
2) EXEC SP to run "this week vs last week" exception report. A giant nasty:

Code:
SELECT cols....
FROM [xyz]
LEFT JOIN [histxyz] ON [xyz].[partnumber] = [xyzhist].[partnumber]
WHERE [xyz].[cola] <> [histxyz].[cola]
OR [xyz].[colb] <> [histxyz].[colb]
OR etc...

through each of the fieleds that are hooked up to change tracking. Run that SP once, then use that temp table to generate customized reports based on parts per product which had a change.

3) Update weekly state snapshot of all parts remembering this week's state... transfer data from [xyz] to [xyzhist], so TRUNCATE then INSERT commands.

Seems slow and monotonous, the snapshotting "shell game" aspect... perhaps I may wrap that all into a transfer SP and allow the data to stay right on the server as it moves tables.