Need to create Period over Period Issue Reporting in SQL Server 2016

Question

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!

Answer 1

等待大神答复