A (hopefully) simplified idea of what I'm looking to do.
- 1st, is it possible?
- 2nd, if it is, how do I do it?
- 3rd, if it isn't possible or if there is a better way to get the same result, please help me out. I am far from a DBA so please be gentle. Thank you!
I create a VIEW with calculations pulling from TableA and TableB. I want that VIEW to be mirrored as TableC and updated with new rows as new rows are added to the VIEW.
Again, this is an oversimplification of something I have working up to the VIEW... the important part is having that VIEW turn into a TableC and continuing to be identical with UPDATES because my VIEW is taking FOREVER to load as new rows are being added and more calculations are taking place. The calculations will take place during the UPDATE and then all future SELECT statements on TableC will no longer perform calculations
System Data Table A
| TableA.ID | TableA.Date | TableA.QUESTION1 | TableA.ANSWER1 |
|---|---|---|---|
| 1 | 1/1/2022 | How Many Units? | 10 |
| 2 | 1/15/2022 | How Many Units? | 25 |
| 3 | 1/27/2022 | How Many Units? | 45 |
System Data Table B
| TableB.ID | TableB.Date | TableB.QUESTION | TableB.ANSWER |
|---|---|---|---|
| 1 | 1/1/2022 | How Many Hours? | 30 |
| 2 | 1/15/2022 | How Many Hours? | 55 |
| 2 | 1/27/2022 | How Many Hours? | 92 |
VIEW (TableA Answers Row - Previous Row) and (TableB Answers Row - Previous Row)
| Date | Difference in Units? | Difference in Hours? | Formula |
|---|---|---|---|
| 1/15/2022 | 25-10=15 | 55-30=25 | (15*25)/365=1.0274 |
| 1/27/2022 | 45-25=20 | 92-55=37 | (20*37)/365=2.0274 |
Table C (duplicate of VIEW... and I'll also show it without the formulas which are actually part of the SELECT Statement behind the VIEW rather than what appears in the rows)
| Date | Difference in Units? | Difference in Hours? | Formula |
|---|---|---|---|
| 1/15/2022 | 15 | 25 | 1.0274 |
| 1/27/2022 | 20 | 37 | 2.0274 |
