Loop In a Select
-
Hey all,
Is it possible to have a loop in a select statement or do I have to use cursors?
Suppose I have a table
Create Table #test
(
x INT, y INT, total INT
)
May contain the values at present:
1, 5, NULL
2, 7, NULL
3, 4, NULL
1, 6, NULL
I want to update the total from the x and y columns. Given the formula:
total = 100 / 2^x + 100 / 2^(x+1) + 100 / 2^(x+2) ... 100 / 2^y
So in theory it is the total of 100 / 2 to the power of x through to y.
Can this be done from a SELECT?
Or is it a case of writing a cursor or a loop in plain old SQL?
char z;
fernandoss
-
- declare
@zeno table(PK int identity primary key, x INT, y INT, total INT)
- insert
@zeno(x,y)
- select 1, 1 union all
- select 1, 2 union all
- select 1, 3 union all
- select 1, 4 union all
- select 1, 5 union all
- select 2, 7 union all
- select 3, 4 union all
- select 1, 6
- --create a numbers table: can often be used to avoid loops
- declare
@numbers table(num int identity,nul tinyint)
- insert
@numbers(nul) select null from syscolumns
- update
z
- set
total = V.total
- from
@zeno z
- join
- (
- select
PK, sum(100/power(2, num)) total
- from
@zeno t
- join
@numbers n
- on n.num between t.x and t.y
- group
by PK
- )
V
- on
z.PK = V.PK
- select
* from @zeno
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Thanks for that Tim.
Nice thinking...
-
Paul
Here's a solution that doesn't use a numbers table. It will work in your situation but it may be fairly difficult to generalise!
First off, I assume you have a constraint or something else that checks that x < y? If so, a bit of mathematical analysis reveals that 1/2^x + ... + 1/2^y is equivalent to 2^(1-x) - 2^(-y). So then your query is:
Create Table #test
(
x INT, y INT, total INT null
)
insert into #test
select 1, 5, null union all
select 2, 7, null union all
select 3, 4, null union all
select 1, 6, null
update #test set total = 100 * ( power(2.0000, 1 - x) - power(2.0000, -y) )
select * from #test
--Result set
x y total
----------- ----------- -----------
1 5 96
2 7 49
3 4 18
1 6 98
This method also has the advantage that, since it only adds (or subtracts) two values in the expression, there is less room for rounding errors than if you evaluate every value from x to y individually. Bear in mind, however that, since you have defined your total column as int, you don't get exact values.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply