Search code examples
sqlsql-serversql-server-2016

SQL - calculate total value of group, row by row

I have a fact sales table that is structured like the below. This contains information on subscriptions (sales) by customer.

CustomerKey SubscriptionKey StartDate End date Value
385884 1256 2020-01-17 00:00:00.000 2025-06-17 00:00:00.000 200
385884 2346 2020-03-11 00:00:00.000 2022-06-10 00:00:00.000 400
385884 5433 2022-10-17 00:00:00.000 2024-07-17 00:00:00.000 500

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..

CustomerKey SubscriptionKey StartDate End date Value ValueAtEndDate
385884 1256 2020-01-17 00:00:00.000 2025-06-17 00:00:00.000 200 200
385884 2346 2020-03-11 00:00:00.000 2022-06-10 00:00:00.000 400 600
385884 5433 2022-10-17 00:00:00.000 2024-07-17 00:00:00.000 500 700

So what it needs to do is look at "active" subscription by CustomerKey.. and then calculate the total value of any active subscriptions at the time of [End date]. This is done if the [End fate] falls between the [startdate] and end date of another subscription associated to that contract.

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.

SQL Server LAG() function to calculate differences between rows

Image example of below solution.. enter image description here

Solution 
  • select *
from T t1 cross apply (
    select sum(Value) from T t2
    where t2.CustomerKey = t1.CustomerKey
        and t1.EndDate between t2.StartDate and t2.EndDate
) v(ValueAtEndDate);

    This could be just a scalar subquery. Either way is essentially the same.

    https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=aa500f79e17b5681410f1e6ce8464551