April 2, 2003 at 1:01 pm
Hey....
I'm easily able to use the SUM aggregate function to "SUM" data from row 1 to row N..
But what if I want the "Product" of numbers from row 1 to Row N ???
I need to do basic Financial performance calculations which requires such multiplication and cannot offhand think of a simple "Set Based" way to do it. I'm staring a Cursor straight in the face.
Any suggestions ????
April 3, 2003 at 1:39 am
If by product you mean row1.numb * row2.numb etc. Then
declare @r numeric(28,0)
set @r=1
select top 100 @r=@r*numb from tabela
select @r
Can't seem to find a way not to use TOP.
Far away is close at hand in the images of elsewhere.
Anon.
April 3, 2003 at 7:11 am
Most excellent David - thanks.
My performance data ends up looking like this:
ENTITY DATE PERFORMANCE
S&P 500 5/1/2002 .05
S&P 500 5/2/2002 .04
S&P 500 5/3/2002 .09
S&P 500 5/4/2002 .1
S&P 500 5/5/2002 -.05
This data is essentially a weeks worth of performance. To calculate the "Weeks Performance" - I add one to each performance number, then multiply times 1 + the next performance number, and so on....
Then - after all multiplications are done - subtract 1 from the result. So...
DECLARE @Result decimal(38, 7)
SET @Result = 1
SELECT
@Result = @Result * (PERFORMANCE+ 1)
FROM
PerformanceTable
WHERE
DATE between '5/1/2002' and '5/1/2002'
AND ENTITY = 'S&P 500'
SELECT @Result - 1
And I come up with this: .2438430
which is correct.
So - if I wrap this in a function which receives the Entity to check, and Begin and End dates - I'm all set for weekly, monthly, quarterly etc.. performance.
Thanks again and I've only done brief testing - but this should do it. !!!!
April 3, 2003 at 8:26 am
Actually - spoke with a Finance guy and he has a slightly different way which is additive and uses LOG and EXP - but the result is exactly the same. Still have to add one to each number and then subtract 1 from the end result - but I can use the super fast SUM intrinsic function rather than multiplication. - here it is:
DECLARE @Result decimal(38, 7)
SELECT
@Result = SUM(LOG((Perf + 1)))
FROM
Performance
WHERE
id between 1 and 5
/* id is the SerialID of a date value */
SET @Result = EXP(@Result) - 1
SELECT @Result
This works the same way put is probably more effecient and less prone to error - CERTAINLY more so than using a cursor.
- B
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply