replicate sumproduct function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

replicate sumproduct function

Author

Topic

DanielS
Starting Member

32 Posts

Posted - 2013-11-28 : 01:32:37

Hello all, I’m trying to replicate the sumproduct function from Excel within SQL, to be used for compounding returns.

I currently have the following;

select ID,
EXP(SUM(LOG(A_RETURN))) as B_RETURN
from
(
select ID,
1+DAILY_RETURN as A_RETURN
from Table
) as A
group by ID

The above code works fine, it gives me the correct compounded return for each ID.

However, I’m looking to also have a DATE within the code and to show the running compounded value for each day. The code I’m using below does not provide the correct results.

select ID,
TO_DATE,
EXP(SUM(LOG(A_RETURN))) as B_RETURN
from
(
select ID,
TO_DATE,
1+DAILY_RETURN as A_RETURN
from Table
) as A
group by ID, TO_DATE

Here's my data and what I'm trying to achieve. The first 2 columns, ID and DAILY_RETURN is the data in my table, A_RETURN and B_RETURN is what I’m trying to calculate.

ID DAILY_RETURN A_RETURN B_RETURN
A 0.03 1.03 1.030000
A 0.02 1.02 1.050600
A -0.01 0.99 1.040094
B 0.035 1.035 1.035000
B -0.022 0.978 1.012230
B 0.011 1.011 1.023365

Any assistance would be greatly appreciate.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-28 : 02:40:55

[code]select ID, DAILY_RETURN, 1+DAILY_RETURN as A_RETURN, B_RETURN
from Table A
CROSS APPLY
(
select EXP(SUM(LOG(1+DAILY_RETURN))) as B_RETURN
from Table x
where x.ID = A.ID
and x.TO_DATE <= A.TO_DATE
) B
order by ID, TO_DATE
[/code]

KH
[spoiler]Time is always against us[/spoiler]