0

I am responsible for creating period-over-period and trend reporting for our Team's Issue Management Department. What I need to do is at copy table Issues at month-end into a new table IssuesHist and add a column with the current date example: 1/31/21. Then at the next month-end I need to take another copy of the Issues table and append it to the existing IssuesHist table, and then add the column again with the current date. For example: 2/28/21.

I need to do this to be able to run comparative analysis on a period-over-period basis. The goal is to be able to identify any activity (opening new issues, closing old ones, reopening issues, etc.) that occurred over the period.

Example tables below:

Issues Table with the current data from our front-end tool

I need to copy the above into the new IssuesHist and add a date column like so

Then at the following month end I need to do the same thing. For example if the Issues table looked like this (changes highlighted in Red)

I would need to Append that to the bottom of the existing IssuesHist table with the new Date. So that I could run queries comparing the data periods to identify any changes.

My research has shown that a Temporal Table may be the best solution here, but I am unable to DIM our existing database's tables to include system versioning.

Please let me know what solution would work, best, and if you have any SQL Statement Tips.

Thank you!

IA_Data
  • 1
  • Hi - why do you need a current and a history table? Why not use one table with a date column? – NickW Jan 11 '21 at 10:20
  • Hi @NickW we would need a current and history table. We couldn't store all the data in 1 table w/ a date column because we need to have current reporting on a daily and intra-day basis. – IA_Data Jan 12 '21 at 13:47
  • @NickW would it be possible to have sql server automatically add the current date in a date column when copying to the history table? – IA_Data Jan 12 '21 at 13:48
  • I'm still not clear why having all your data (with appropriate date/time columns) in one table would stop you reporting on daily and intra-day basis? For your second question/comment, you can add the current date automatically to a column by setting it as the default in the table definition: e.g. set the column default to GETUTCDATE() or GETDATE() (be careful of time zones, if applicable) – NickW Jan 12 '21 at 15:19
  • Hi @NickW so while we need current reporting (daily / intra-day) we also need point-in-time reporting for the same data. So we have an issues table that has the current data of what is opened / closed / etc. Then at month end we need to take a snapshot of the data for issues and be able to compare it to previous months. making more sense? – IA_Data Jan 13 '21 at 16:06
  • Cheers - the fact that you are taking month-end snapshots means it all makes sense now :) Are you building a dimensional model? If you are then building a transactional fact table (for your daily reporting) and then inserting data from that into your monthly snapshot table should be quite straightforward – NickW Jan 13 '21 at 16:56
  • @NickW sorry it took so long to explain! I am fairly new to SQL! That is exactly what we are trying to do through an automated script that run on last day of the month, and would add a "Date" column when inserting into the monthly snapshot table. – IA_Data Jan 14 '21 at 13:00

0 Answers0