I have a dataset where I need to calculate a value that for each row depends on the value in the previous row of the same column. Or a 1 initially when there is no previous row. I need to do this on different partitions.
The formula looks like this: factor = (previous factor or 1 if it does not exist) * (1 + div / nav) This needs to be partitioned by Inst_id.
I would prefer to avoid a cursor. Maybe cte with recursion - but I cannot get my head around it - or another way?
I know this code does not work as I cannot reference the same column, but it is another way of showing what I'm trying to do:
SELECT Dato, Inst_id, nav, div
, (1 + div / nav ) * ISNULL(LAG(factor, 1) OVER (PARTITION BY Inst_id ORDER BY Date), 1) AS factor
FROM @tmp
So with my test data I need to get these results in the factor column below. Please ignore rounding issues, as I calculated this in Excel:
date Inst_id nav div factor
11-04-2012 16 57.5700 5.7500 1.09987841
19-04-2013 16 102.8600 10.2500 1.20948130
29-04-2014 16 65.9300 16.7500 1.51675890
08-04-2013 29 111.2736 17.2500 1.15502333
10-04-2014 29 101.9650 16.3000 1.33966395
15-04-2015 29 109.5400 7.5000 1.43138825
27-04-2016 29 94.2500 0.4000 1.43746311
15-04-2015 34 159.1300 11.4000 1.07163954
27-04-2016 34 124.6100 17.6000 1.22299863
26-04-2017 34 139.7900 9.2000 1.30348784
01-04-2016 38 99.4600 0.1000 1.00100543
26-04-2017 38 102.9200 2.1000 1.02143014
Test data:
DECLARE @tmp TABLE(Dato DATE, Inst_id INT, nav DECIMAL(26,19), div DECIMAL(26,19), factor DECIMAL(26,19))
INSERT INTO @tmp (Dato, Inst_id, nav, div) VALUES
('2012-04-11', 16, 57.57, 5.75),
('2013-04-19', 16, 102.86, 10.25),
('2014-04-29', 16, 65.93, 16.75),
('2013-04-08', 29, 111.273577, 17.25),
('2014-04-10', 29, 101.964994, 16.3),
('2015-04-15', 29, 109.54, 7.5),
('2016-04-27', 29, 94.25, 0.4),
('2015-04-15', 34, 159.13, 11.4),
('2016-04-27', 34, 124.61, 17.6),
('2017-04-26', 34, 139.79, 9.2)
I'm on a Microsoft SQL Server Enterprise 2016 (and use SSMS 2016).
You can use (if DIV and NAV are always >0):
SELECT A.* , EXP(SUM( LOG(1+DIV/NAV) ) OVER (PARTITION BY INST_ID ORDER BY DATO) )AS FACT_NEW
FROM @tmp A
Actually what you need is an equivalent of aggregate function MULTIPLY() OVER .... Using a log theorem: LOG(M*N) = LOG(M) + LOG (N) you can do it; for example:
DECLARE @X1 NUMERIC(10,4)=5
DECLARE @X2 NUMERIC(10,4)=7
SELECT @x1*@x2 AS S1, EXP(LOG(@X1)+LOG(@X2)) AS S2
Output:
+------------+---------+-------------------------+------------------------+--------+------------------+
| Dato | Inst_id | nav | div | factor | FACT_NEW |
+------------+---------+-------------------------+------------------------+--------+------------------+
| 2012-04-11 | 16 | 57.5700000000000000000 | 5.7500000000000000000 | NULL | 1.099878408893 |
| 2013-04-19 | 16 | 102.8600000000000000000 | 10.2500000000000000000 | NULL | 1.20948130303111 |
| 2014-04-29 | 16 | 65.9300000000000000000 | 16.7500000000000000000 | NULL | 1.51675889783963 |
| 2013-04-08 | 29 | 111.2735770000000000000 | 17.2500000000000000000 | NULL | 1.155023325977 |
| 2014-04-10 | 29 | 101.9649940000000000000 | 16.3000000000000000000 | NULL | 1.33966395090911 |
| 2015-04-15 | 29 | 109.5400000000000000000 | 7.5000000000000000000 | NULL | 1.43138824917236 |
| 2016-04-27 | 29 | 94.2500000000000000000 | 0.4000000000000000000 | NULL | 1.43746310646293 |
| 2015-04-15 | 34 | 159.1300000000000000000 | 11.4000000000000000000 | NULL | 1.071639539998 |
| 2016-04-27 | 34 | 124.6100000000000000000 | 17.6000000000000000000 | NULL | 1.22299862758278 |
| 2017-04-26 | 34 | 139.7900000000000000000 | 9.2000000000000000000 | NULL | 1.30348784264639 |
+------------+---------+-------------------------+------------------------+--------+------------------+