I want to build a query which allows me to flexible linear extrapolate a number down to Age 0 starting from the last known value. The table (see below) has two columns, column Age and Volume. My last known volume is 321.60 at age 11, how can I linear extrapolate the 321.60 down to age 0 in annual steps? Also, I would like to design the query in a way which allows the age to change. For example, in another scenario the last volume is at age 27. I have been experimenting with the lead function, as a result I can extrapolate the volume at age 10 but the function does not allow me to extrapolate down to 0. How can I design a query which (A) allows me to linear extrapolate to age 0 and (B) is flexible and allows different starting points for the linear extrapolation.
SELECT [age],
[volume],
Concat(CASE WHEN volume IS NULL THEN ( Lead(volume, 1, 0) OVER (ORDER BY age) ) / ( age + 1 ) *
age END, volume) AS 'Extrapolate'
FROM tbl_volume
+-----+--------+-------------+
| Age | Volume | Extrapolate |
+-----+--------+-------------+
| 0 | NULL | NULL |
| 1 | NULL | NULL |
| 2 | NULL | NULL |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
| 6 | NULL | NULL |
| 7 | NULL | NULL |
| 8 | NULL | NULL |
| 9 | NULL | NULL |
| 10 | NULL | 292.363 |
| 11 | 321.60 | 321.60 |
| 12 | 329.80 | 329.80 |
| 13 | 337.16 | 337.16 |
| 13 | 343.96 | 343.96 |
| 14 | 349.74 | 349.74 |
+-----+--------+-------------+
Advertisement
Answer
If I assume that the value is 0
at 0
, then you can use simple arithmetic. This seems to work in your case:
select t.*,
coalesce(t.volume, t.age * (t2.volume / t2.age)) as extrapolated_volume
from t cross join
(select top (1) t2.*
from t t2
where t2.volume is not null
order by t2.age asc
) t2;
Here is a db<>fiddle