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

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

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.

CodePudding user response：

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

CodePudding user response：

You can use a scalar subquery to compute the extra column. For example:

select t.*,
  t.value   (
    select sum(value)
    from t u
    where u.end_date between t.startdate and t.enddate 
      and u.susbscriptionkey <> t.susbscriptionkey 
      and u.customerkey = t.customerkey
  ) as vaed
from t