Calculate running product with custom expression in SQL Server 2005

Question

I have two tables, first table section with schema as:

SecID  |      Date     |   SecReturn
-------|---------------|--------------
  208  |   2015-04-01  |   0.00355
  208  |   2015-04-02  |   -0.00578
  208  |   2015-04-03  |   0.00788
  208  |   2015-04-04  |   0.08662
  105  |   2015-04-01  |   0.00786

and the second table SectionDates with schema as:

SecID |  MonthlyDate  |  DailyDate
------|---------------|-------------
208   |   2015-04-02  |  2015-04-03
105   |   2015-04-01  |  2015-04-01

I want to calculate the running product on SecReturn column of the table Section with date range (DailyDate to MonthlyDate) from second table SectionDates.

Running product will be calculated for each sectionID based on formula :

Date       |   SecReturn   |  SectionTotal
-----------|---------------|--------------------
2015-04-01 |  X (lets say) | (1+x)-1
2015-04-01 |  Y            | (1+x)(1+y)-1
2015-04-01 |  Z            | (1+x)(1+y)(1+z)-1

After applying above calculation values will be computed in SectionTotal column as for date 2015-04-01 computed value will be (1+0.00355)-1. Similarly, for date 2015-04-02 computed value will be (1+0.00355)(1+-0.00578)-1 and for date 2015-04-03 computed value will be (1+0.00355)(1+-0.00578)(1+0.00788)-1 and so on.

The final output:

 SecID |  Date      |   SectionTotal 
-------|------------|-----------------  
  105  | 2015-04-01 |  0.00786          
  208  | 2015-04-01 |  0.00355          
  208  | 2015-04-02 |  -0.0022505       
  208  | 2015-04-03 |  0.0056117

Hello, welcome to StackOverflow! Could you please show us what you have attempted so far? — John Odom, Feb 11 '16 at 22:14

score 0 · Answer 1 · edited May 23 '17 at 12:07

0

You can try following query:

SELECT SecID, [Date], [SecReturn], 
       ROUND((1 + SecReturn) * COALESCE(v,1) - 1, 5) AS SectionTotal   
FROM mytable AS t1
OUTER APPLY (  
    SELECT EXP(SUM(LOG(SecReturn + 1))) AS v
    FROM mytable AS t2
    WHERE t1.SecID = t2.SecID AND t1.[Date] > t2.[Date]) AS t3

OUTER APPLY, which is available in SQL Server 2005 AFAIK, fetches all records to be considered in running multiplication calculation.

Using the formula for a multiplication aggregate, found in this post you can obtain desired result.

Demo here

edited May 23 '17 at 12:07

Community

1
1

answered Feb 11 '16 at 21:53

Giorgos Betsos

71,379
9
63
98

1

It should be COALESCE(v,1) and I would consider adding ROUND() to account for the loss of precision that's going to result from the EXP function. EDIT: actually it could be done in the same way without the outer apply in this way - `ROUND((SELECT EXP(SUM(LOG(1+[SecReturn]))) FROM mytable WHERE [SecID] = t1.[SecID] AND [Date] <= t1.[Date]) - 1, 5)` – ZLK Feb 11 '16 at 22:29
1

You should note that this O(n²) and inefficient for large tables. In the absense of windowing constructs in later versions of SQL Server, sometimes a `CURSOR` can be more efficient (depending on the size of the input). – TT. Feb 12 '16 at 06:31

Calculate running product with custom expression in SQL Server 2005

1 Answers1