Prev: sql commands for datediff
Next: Diferent times for execution the same code
From: Frank on 13 Jan 2010 09:45
Hi,

Is it possible to apply a calculation to all the values in a column
without using a WHILE loop or a CURSOR?
I have the following example values in a column:

0.029000
0.012000
0.038000
0.011000
-0.048000

And I want to perform the following calculation on the values:

result = (1 + value1) * (1 + value2) * (1 + value3) * (1 + value4) *
(1 + value5) - 1;

I can create a pivot and sum the values that way, which is easy with 5
or so values.
But I also have a calculation that includes 60 values, which makes a
pivot a bit tedious.

Is it possible to use CROSS APPLY and a function to do this?
If it is, I can't figure out how to return the value of one part of
the sum to add to the next.

Many thanks,
Frank.
From: Bob Barrows on 13 Jan 2010 10:48
Frank wrote:
> Hi,
>
> Is it possible to apply a calculation to all the values in a column
> without using a WHILE loop or a CURSOR?
> I have the following example values in a column:
>
> 0.029000
> 0.012000
> 0.038000
> 0.011000
> -0.048000
>
> And I want to perform the following calculation on the values:
>
> result = (1 + value1) * (1 + value2) * (1 + value3) * (1 + value4) *
> (1 + value5) - 1;
>
> I can create a pivot and sum the values that way, which is easy with 5
> or so values.
> But I also have a calculation that includes 60 values, which makes a
> pivot a bit tedious.
>
> Is it possible to use CROSS APPLY and a function to do this?
> If it is, I can't figure out how to return the value of one part of
> the sum to add to the next.
>
My first inclination is a CLR Aggregation function. What version of SQL
are you using? If 2000, we can forget that idea.

--
HTH,
Bob Barrows


From: Tom Cooper on 13 Jan 2010 11:02
It depends on what the calculation is. Of course, SUM is easy because SQL
has a SUM() function. You, however, are doing a product and there is no
equivalent function. However, you may be able to use the fact that if you
take the EXP() of the SUM() of the LOG() of your values, that is the same as
the Product. This is easy if you know all the values you are multiplying
are greater than zero. For your example,

Declare @Calc Table (TheValue float);
Insert @Calc (TheValue)
Select 0.029000
Union All Select 0.012000
Union All Select 0.038000
Union All Select 0.011000
Union All Select -0.048000

Select Exp(Sum(Log(1 + TheValue))) - 1
From @Calc;

However, you will get an error if you try to take the LOG() of a value that
is negative or zero. So, if you might be multiplying negative factors, then
you need to take the LOG() of the ABS() of your factors, then count the
number of negative factors so you know whether the final result is positive
or negative. That would be

Select (Case When Sum(Case When 1 + TheValue < 0 Then 1 Else 0 End) % 2 = 0
Then 1 Else -1 End) *
(Exp(Sum(Log(Abs(1 + TheValue))))) - 1
From @Calc;

If some of the values you are multipling can be zero, you would need more
code to handle that case.

Tom
"Frank" <francis.moore(a)gmail.com> wrote in message
news:d2702ece-846c-4019-a7cc-e0dd955c1a62(a)h9g2000yqa.googlegroups.com...
> Hi,
>
> Is it possible to apply a calculation to all the values in a column
> without using a WHILE loop or a CURSOR?
> I have the following example values in a column:
>
> 0.029000
> 0.012000
> 0.038000
> 0.011000
> -0.048000
>
> And I want to perform the following calculation on the values:
>
> result = (1 + value1) * (1 + value2) * (1 + value3) * (1 + value4) *
> (1 + value5) - 1;
>
> I can create a pivot and sum the values that way, which is easy with 5
> or so values.
> But I also have a calculation that includes 60 values, which makes a
> pivot a bit tedious.
>
> Is it possible to use CROSS APPLY and a function to do this?
> If it is, I can't figure out how to return the value of one part of
> the sum to add to the next.
>
> Many thanks,
> Frank.

From: Dan on 13 Jan 2010 11:03

"Frank" <francis.moore(a)gmail.com> wrote in message
news:d2702ece-846c-4019-a7cc-e0dd955c1a62(a)h9g2000yqa.googlegroups.com...
> Hi,
>
> Is it possible to apply a calculation to all the values in a column
> without using a WHILE loop or a CURSOR?
> I have the following example values in a column:
>
> 0.029000
> 0.012000
> 0.038000
> 0.011000
> -0.048000
>
> And I want to perform the following calculation on the values:
>
> result = (1 + value1) * (1 + value2) * (1 + value3) * (1 + value4) *
> (1 + value5) - 1;
>
> I can create a pivot and sum the values that way, which is easy with 5
> or so values.
> But I also have a calculation that includes 60 values, which makes a
> pivot a bit tedious.
>
> Is it possible to use CROSS APPLY and a function to do this?
> If it is, I can't figure out how to return the value of one part of
> the sum to add to the next.
>
> Many thanks,
> Frank.

I'm not sure this is right, and I'm expecting at least one reply suggesting
that it won't necessarily work on anything but SQL Server, but how about
this:

create table testing (value decimal(10,8))

insert into testing values (0.029000)
insert into testing values ( 0.012000)
insert into testing values ( 0.038000)
insert into testing values ( 0.011000)
insert into testing values ( -0.048000)


declare @myvalue decimal(20,18)
set @myvalue = 1

select @myvalue = @myvalue * (1 + value) from testing

print @myvalue - 1

drop table testing



Just adjust the decimal declarations to fit your data.

--
Dan

From: Frank on 13 Jan 2010 11:05
Hi Bob,

Thanks for the response.
I forgot to add that I'm using SQL Server 2005.
The rest of the calculations are done in SQL so it probably doesn't
make sense to go down the CLR route yet.
I've also been playing around with a CTE but still no luck.
I'll stick with a cursor for the time being until I can figure out a
better way of doing it.

Thanks,
Frank.
 |  Next  |  Last
Pages: 1 2 3
Prev: sql commands for datediff
Next: Diferent times for execution the same code