0

I have a company with different departments, let's say sales, accouting and hr.

For each of them, I would have several particular key performance indicators and for each KPI, a hoped goal with the actual result. In my application, I would set the hoped goals, my users would simply input the actual result, and at the end of the month, I would create a snapshot.

In the application, I should be able to navigate month by month in the past and check their respective information as readonly.

I am trying to figure out a proper way to store monthly snapshots of my tables. Basically, something that would allow a "as-at" view over time.

Should I use a separate table to store historical data such as Sales_History, and keep an original table for the current, active month?

Or rather have only one set of tables and set a timestamp field for each row which would allow me to select the data with a timestamp for the requested month? then, when inputing a value, I update the row with the timestamp in the current month, and when I create a snapshot, I would create new rows with a timestamp for the next month. In any case, there would not be a huge volume of data.

I don't know that there is a particular standard way of doing it but I would appreciate your input as I'm sure it will make my life easier when I start coding.

Louitabitbol
  • 21
  • 5

1 Answers1

0

I see two approaches to this:

  1. Create a separate table in which you would have the following attributes: Year, Month, Goal, Result. Every time you set a goal for a particular month of a year, you'd insert a row into this table with the Result attribute being NULL. Then you might even make an SQL Server Agent job which runs on the 1st of every month which would go to the, for example, Sales table, pick up the final result of the prior month and update the new table you created. This way you would have your targets and your results in one table and they wouldn't be prone to changes in the "master" table.

  2. Do everything like above except for the Result attribute. So, you'd have a new table with the attributes Year, Month, Goal and every time you want to compare goals and results, you'd calculate results on the fly selecting something along the line of:

    • FIRST_VALUE(Result) OVER (ORDER BY RowTimestamp DESC) AS Result if the result is something that is being repeatedly inserted,
    • SUM(Result) AS Result if the result is actually a sum of all sales,
    • another aggregate function that makes sense in your use case (I can't be more precise since I can't tell for sure, by your question, how does the initial table look like).

Performance of the new table shouldn't be an issue either way, just put an index on (Year, Month) - preferably unique clustered index. Also, make the data types themselves as small as possible to improve upon it even further... tinyint for Month and smallint for Year.

MK_
  • 1,139
  • 7
  • 18