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:d2702ece846c4019a7cce0dd955c1a62(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:d2702ece846c4019a7cce0dd955c1a62(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 