title: SQL - calculate total value of group, row by row
body: <p>I have a fact sales table that is structured like the below. This contains information on subscriptions (sales) by customer.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CustomerKey</th>
  <th>SubscriptionKey</th>
  <th>StartDate</th>
  <th>End date</th>
  <th>Value</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>385884</td>
  <td>1256</td>
  <td>2020-01-17 00:00:00.000</td>
  <td>2025-06-17 00:00:00.000</td>
  <td>200</td>
  </tr>
  <tr>
  <td>385884</td>
  <td>2346</td>
  <td>2020-03-11 00:00:00.000</td>
  <td>2022-06-10 00:00:00.000</td>
  <td>400</td>
  </tr>
  <tr>
  <td>385884</td>
  <td>5433</td>
  <td>2022-10-17 00:00:00.000</td>
  <td>2024-07-17 00:00:00.000</td>
  <td>500</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to add a row which shows me at the time of end date, the total value of a customer so the business can use the total value to determine whether the customer is worth renewing or not. So based on the above table it would look like this..</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CustomerKey</th>
  <th>SubscriptionKey</th>
  <th>StartDate</th>
  <th>End date</th>
  <th>Value</th>
  <th>ValueAtEndDate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>385884</td>
  <td>1256</td>
  <td>2020-01-17 00:00:00.000</td>
  <td>2025-06-17 00:00:00.000</td>
  <td>200</td>
  <td>200</td>
  </tr>
  <tr>
  <td>385884</td>
  <td>2346</td>
  <td>2020-03-11 00:00:00.000</td>
  <td>2022-06-10 00:00:00.000</td>
  <td>400</td>
  <td>600</td>
  </tr>
  <tr>
  <td>385884</td>
  <td>5433</td>
  <td>2022-10-17 00:00:00.000</td>
  <td>2024-07-17 00:00:00.000</td>
  <td>500</td>
  <td>700</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>So what it needs to do is look at &quot;active&quot; subscription by CustomerKey.. and then calculate the total value of any active subscriptions at the time of <strong>[End date]</strong>. This is done if the <strong>[End fate]</strong> falls between the <strong>[startdate]</strong> and end date of another subscription associated to that contract.</p>
  <p>I have tried the method below but really can't get my head around the best way of even approaching this.. so any tips or just pointers in the right direction would be appreciated.</p>
  <p><a href="https://stackoverflow.com/questions/52220109/sql-server-lag-function-to-calculate-differences-between-rows">SQL Server LAG() function to calculate differences between rows</a></p>
  <p>Image example of below solution..
  <a href="https://i.stack.imgur.com/3t8kO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3t8kO.png" alt="enter image description here" /></a></p>