Cursor Loop Results into a table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Cursor Loop Results into a table?

Author

Topic

kkassie07
Starting Member

1 Post

Posted - 2012-06-19 : 18:45:19

Okay so I have a working cursor loop, now I just need to find out how to take the results it generates and put them into it's own table. Here's the loop below: I'm completely stumped on this one.

--Call Completion Rate with Run Name

declare @RunName varchar(250);
declare @zc bigint;
declare @one bigint;
declare @two bigint;
declare @three bigint;
declare @four bigint;
declare @five bigint;
declare @total bigint;
declare @sum bigint;
declare @Run_No bigint;
declare RunLoop cursor for select Run_No, (Radio_Type + ', ' + Installation + ', ' + Movement) AS RunName from Test_Info
open RunLoop
fetch next from RunLoop
into @Run_No, @RunName
while @@Fetch_Status = 0
BEGIN

set @zc = (select count (*) as zero from Raw_Voice where T_R ='r' and score = 0 and Run_No = @Run_No)
set @one = (select count (*) as one from Raw_Voice where T_R ='r' and score = 1 and Run_No = @Run_No)
set @two =(select count (*) as two from Raw_Voice where T_R ='r' and score = 2 and Run_no = @Run_No)
set @three = (select count (*) as three from Raw_Voice where T_R ='r' and score = 3 and Run_no = @Run_No)
set @four = (select count (*) as four from Raw_Voice where T_R ='r' and score = 4 and Run_no = @Run_No)
set @five = (select count (*) as five from Raw_Voice where T_R ='r' and score = 5 and Run_no = @Run_No)
set @total = (select COUNT(*) as total from raw_voice where T_R = 'r' and Run_no = @Run_No)

set @sum = @zc+@one+@two+@three+@four+@five

select @RunName as Run_Name, @zc as zero , @one as one , @two as two , @three as three, @four as four , @five as five , @total as total , @sum as sum

FEtch Next from RunLoop into @Run_no, @RunName

end
close RunLoop
Deallocate Runloop

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-19 : 19:43:55

You don't need a cursor for this:

SELECT (T.Radio_Type + ', ' + T.Installation + ', ' + T.Movement) AS RunName,
SUM(CASE WHEN V.Score=0 THEN 1 END) Zero,
SUM(CASE WHEN V.Score=1 THEN 1 END) One,
SUM(CASE WHEN V.Score=2 THEN 1 END) Two,
SUM(CASE WHEN V.Score=3 THEN 1 END) Three,
SUM(CASE WHEN V.Score=4 THEN 1 END) Four,
SUM(CASE WHEN V.Score=5 THEN 1 END) Five,
COUNT(*) Total,
SUM(CASE WHEN V.Score BETWEEN 0 AND 5 THEN 1 END) [Sum]
INTO MyNewTable
FROM Test_Info T
INNER JOIN Raw_Voice V ON T.Run_no=V.Run_no
WHERE V.T_R='r'
GROUP BY (T.Radio_Type + ', ' + T.Installation + ', ' + T.Movement), T.Run_no

That will put the results into MyNewTable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources