Pro rata allocation of contract values over contract timeframe, for each calendar year within a large timeframe

Clementine Sin 0
Viewed 20

We have a dataset of contracts, with columns indicating counterparty, value, start date, and end date.

We are looking for a summary of total contract value, per counterparty, per calendar year.

Before we could apply a GROUP BY to the data, we would need a calculated column for each calendar year, with the contract value assigned pro rata.

Example: start date 30/06/2015, end date 31/12/2017, contract value €500.000

  • the contract is about 2,5 years, so a value of €200.000 [€500.000 / 2,5] is allocated (pro rata) per year.
  • in the year 2015, the value is for half a year, so is assigned about €100.000
  • in the year 2016, the value is for a full year, so is assigned about €200.000
  • in the year 2017, idem, so the value assigned is about €200.000

(The values in the example are not exact but simplified for illustrative purposes, as the time between 30/06/2015 and 31/12/2015 for instance is not exactly half a year, but this goes beyond the purpose of the issue)

There is no fixed contract length; some span years, others are daily or hourly (the dates are of data type 'timestamp').

How can we do this efficiently, without having to write a select clause for every single calendar year? The start and end dates namely span several decades.

The Oracle version is 19c.

oracle sql
1 Answers

You can use:

SELECT t.counterparty,
       t.value AS total_value,
       c.year_start,
       ROUND(
         t.value * (year_end - year_start) / (end_date - start_date),
         2
       )AS year_value
FROM   table_name t
       CROSS APPLY (
         SELECT GREATEST(
                  ADD_MONTHS(TRUNC(start_date, 'YY'), 12 * (LEVEL - 1)),
                  start_date
                ) AS year_start,
                LEAST(
                  ADD_MONTHS(TRUNC(start_date, 'YY'), 12 * LEVEL),
                  end_date
                ) AS year_end
         FROM   DUAL
         CONNECT BY ADD_MONTHS(TRUNC(start_date, 'YY'), 12 * (LEVEL - 1)) < end_date
       ) c

Which, for the sample data:

CREATE TABLE table_name (counterparty, value, start_date, end_date) AS
SELECT 'A', 500000, DATE '2015-06-30', DATE '2017-12-31' FROM DUAL;

Outputs:

COUNTERPARTY TOTAL_VALUE YEAR_START YEAR_VALUE
A 500000 2015-06-30 00:00:00 101092.9
A 500000 2016-01-01 00:00:00 200000
A 500000 2017-01-01 00:00:00 198907.1

Note: amounts are calculated using the ratio of the number of days (and fractional days) in each year compared to the number of days in the entire period. There is more than half-a-year between 2015-06-30 00:00:00 and the start of the next year so it is assigned slightly more than 100,000 value. Similarly, the final year stops at 2017-12-31 00:00:00 which is slightly less than a full year so gets assigned slightly less than 200,000 value.

fiddle

MT00
Related