I've got a table in SQL Server full of data that is a working DB of data collection reports. This table only stores the current state (meaning it doesn't keep historic anything).
There are a few key metrics that I'd like to begin tracking over time from this table. Most notably the table has a user's name, their assignments (one row for each), status values (in progress, scheduled, etc.) and last activity date.
Because the table itself doesn't have any history information, I'm looking for the best way to sample the data on some periodic basis recording the current state values and timestamping it. Once this is done, I'll be able to ask questions via reporting like "How many reports was Joe working on on tuesday and what were their statuses?"
What would be the best way to do this, short of creating a SQL Server table to house the information, writing scripts to do the collection and a report/query interface for all of this?
My google-fu is usually really strong but the general concepts are colliding way too firmly with proper names of things that are completely unrelated, thus I can't find any good info out there.
Anyone got experience with such a process? What can you tell me to get me on the right direction?
Edit: I do not currently collect the aggregate data, nor do I currently have a place to store it, though that could be as simple as a SQL Server table. I'm not going to be able to spend as much time as I'd like supporting this reporting capability, so I was hoping for some type of a packaged solution that some admins can maintain with minimal development efforts.
Best Answer
You can create triggers that are fired upon insert/delete/update in the table to write the data you need in a separate history table. You can then later query this table to create your reports. No need to change the application that is now responsible for maintaining the data.