title: Data Warehouse - Versioning of data
body: <p>I am currently designing a data warehouse for a financial company. While a large amount of the process is quite standard I have been presented with an issue (That I believe only exists in the finance sector) of data events that effect a number of rows and their history that can happen at any time.</p>
  
  <p>To explain the issue better. Assume we have an Account A and other 2 months 4 transactions have occurred that effect its balance, changing it from 10000 to 20000.
  When I run a report for that month its fine it will show the activity that derives that value. 
  Now it gets difficult, a month after I backdate a transaction that effects that balance changing it from 20000 to 15000. </p>
  
  <p>Running a report Before that back dating should tell me the original 20000, but after back dated transaction should tell me the 15,000.</p>
  
  <p>To illustrate better refer to data below. </p>
  
  <hr>
  
  <p><strong>Transactions for September and October</strong></p>
  
  <p>with a back dated transaction on the 28th of October for the 13th of September of $500</p>
  
  <p>and a back dated transaction on the 8th of November for the 17th of September to credit the $-50 </p>
  
  <pre><code>╔═════════════════╦═════════════════════════╦════════╦══════════════════╦═══════════════╦═════════════╦═════════╗
  ║ Key_Transaction ║ SK_TransactionEffective ║ Amount ║ PrincipleBalance ║ SK_ReportDate ║ SK_AsOfDate ║ Version ║
  ╠═════════════════╬═════════════════════════╬════════╬══════════════════╬═══════════════╬═════════════╬═════════╣
  ║               1 ║ 12/09/2018              ║  -1000 ║            20000 ║ 12/09/2018    ║ NULL        ║ 1       ║
  ║               6 ║ 13/09/2018              ║   -500 ║            19500 ║ 13/09/2018    ║ 28/10/2018  ║ 2       ║
  ║               2 ║ 16/09/2018              ║    -50 ║            19950 ║ 16/09/2018    ║ NULL        ║ 1       ║
  ║               7 ║ 16/09/2018              ║    -50 ║            19450 ║ 16/09/2018    ║ 28/10/2018  ║ 2       ║
  ║              12 ║ 16/09/2018              ║     50 ║            19950 ║ 16/09/2018    ║ 8/11/2018   ║ 3       ║
  ║               3 ║ 1/10/2018               ║    250 ║            20200 ║ 30/09/2018    ║ NULL        ║ 1       ║
  ║               8 ║ 1/10/2018               ║    250 ║            19700 ║ 30/09/2018    ║ 28/10/2018  ║ 2       ║
  ║              13 ║ 1/10/2018               ║    250 ║            20200 ║ 30/09/2018    ║ 8/11/2018   ║ 3       ║
  ║               4 ║ 6/10/2018               ║  -1200 ║            19000 ║ 6/10/2018     ║ NULL        ║ 1       ║
  ║               9 ║ 6/10/2018               ║  -1200 ║            17800 ║ 6/10/2018     ║ 28/10/2018  ║ 2       ║
  ║              14 ║ 6/10/2018               ║  -1200 ║            19000 ║ 6/10/2018     ║ 8/11/2018   ║ 3       ║
  ║               5 ║ 22/10/2018              ║    100 ║            19100 ║ 22/10/2018    ║ NULL        ║ 1       ║
  ║              10 ║ 22/10/2018              ║    100 ║            17900 ║ 22/10/2018    ║ 28/10/2018  ║ 2       ║
  ║              15 ║ 22/10/2018              ║    100 ║            19100 ║ 22/10/2018    ║ 8/11/2018   ║ 3       ║
  ║              11 ║ 29/10/2018              ║  -1000 ║            16900 ║ 29/10/2018    ║ NULL        ║ (New)1  ║
  ║              16 ║ 29/10/2018              ║  -1000 ║            18100 ║ 29/10/2018    ║ 8/11/2018   ║ (New)2  ║
  ╚═════════════════╩═════════════════════════╩════════╩══════════════════╩═══════════════╩═════════════╩═════════╝
  </code></pre>
  
  <p>Now when I run a report for September (2018-09-01 to 2018-09-30) I should be V1 or when SK_AsOfDate is NULL</p>
  
  <p>If I run a report for October (2018-10-01 to 2018-10-31) my last record should be (11) with a principle balance of 16900 </p>
  
  <p>And my current Principle balance (As of 2018-11-09) should be calculated as of the balance from (16) with PB of (18100)</p>
  
  <p>I have added the SK_AsOfDate to try and deal with versioning issue, but I am still struggling to see a simple and elegant way to achieve this "What was my balance as of 2018-09-30 that will ignore V2 and V3 alterations.</p>
  
  <p>I want to get this right and luckily am not too far down any path, so suggestions are welcome! I am happy to add as many fields as makes this process simple for reporting out of the other end.</p>