Help with making a loop - 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 2000 Forums

Transact-SQL (2000)

Help with making a loop

Author

Topic

jess
Starting Member

17 Posts

Posted - 2006-12-08 : 16:26:44

Hi,
I inherited some ugly code and am trying to fix it up. I would like to place a whole bunch of the code into a loop.
A simple explanation for what the code does is that it takes surveys and calculates things like the percentage of people that answered yes on question 1 compared to the number of people that answered question 1 for these 100 surveys.
Say there are 90 questions on the survey -- the stored procedure has the same code section pasted 90 times and is only changing the code number each time from 1 up until 90. Sounds like the perfect time to use a loop right?!?
So I'll paste 3 sections below so you get an idea what it's trying to do. I tried placing this into a nice little loop but was having a little trouble... Notice the only things that change each time through the loop are codeX, codeX, and code_number=X.

Can someone figure out a loop that will work for this? On the surface it seemed so easy but when I got into it I found it was a little more challenging.

declare @total int

select @total=count(*)
from [TABLE]
where code1 <>''
IF @total=0
select @total=1

update [OTHER_TABLE]
set num_y=(select (convert(float,count (case when code1='Y'
then 1
end)))/ @total
from [TABLE])
where code_number=1

select @total=count(*)
from [TABLE]
where code2 <>''
IF @total=0
select @total=1

update [OTHER_TABLE]
set num_y=(select (convert(float,count (case when code2='Y'
then 1
end)))/ @total
from [TABLE])
where code_number=2

select @total=count(*)
from [TABLE]
where code3 <>''
IF @total=0
select @total=1

update [OTHER_TABLE]
set num_y=(select (convert(float,count (case when code3='Y'
then 1
end)))/ @total
from [TABLE])
where code_number=3

THANKS!
Jess

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 16:57:54

You do not mention anything specific, but this should give you an idea how to solve your problem.

UPDATE		x
SET		x.num1 = x.myAvg1,
		x.num2 = x.myAvg2,
		x.num3 = x.myAvg3
FROM		YourDestinationTableNameHere x
CROSS JOIN	(
			SELECT	1.0 * SUM(CASE WHEN code1 = 'Y' THEN 1 ELSE 0 END) / COUNT(*) myAvg1,
				1.0 * SUM(CASE WHEN code2 = 'Y' THEN 1 ELSE 0 END) / COUNT(*) myAvg2,
				1.0 * SUM(CASE WHEN code3 = 'Y' THEN 1 ELSE 0 END) / COUNT(*) myAvg3
			FROM	YourSourceTableNameHere	
		) y

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources