Looping through records - 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 2005 Forums

Transact-SQL (2005)

Looping through records

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2011-10-19 : 15:05:21

I have a table (PopulationBondsCashSummary) with data structered like this. What I want to do is loop through each record and for each UserID evaluate the UserCount value. If the @userCount is between 2 and 8 then set the @UserPctg value to the UserPctg value pulled from PopulationBondsCashSummary to set my TOP %.

The error I am running into is on these two lines

SET @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary)
SET @USerPctg = (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary)

The error I am getting is "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

USE [QA]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	DECLARE @Level Int
	SET @Level = 1
       
    DECLARE @numrows Int
	
	DECLARE @i int
	SET @i = 1

	SET @numrows = (SELECT COUNT(*) FROM PopulationBondsCashSummary)

	IF @numrows > 0
		WHILE (@i <= (SELECT COUNT(*) FROM PopulationBondsCashSummary))
		BEGIN
				DECLARE @UserCount Int
				SET @UserCount = (SELECT MAX(UserCount) FROM PopulationBondsCashSummary)
				--PRINT @UserCount;	
	
				DECLARE @USerPctg Dec
				SET @USerPctg = (SELECT MAX((CAST(USerPctg * 100 AS Int))) FROM PopulationBondsCashSummary)				
				--PRINT @UserPctg	
				
				SELECT  UserID, UserCount, USerPctg
				FROM    PopulationBondsCashSummary

						SELECT TOP (CASE  
							WHEN @Level = 1 AND @UserCount BETWEEN 2 AND 8 THEN @USerPctg
							WHEN @Level = 1 AND @UserCount BETWEEN 9 AND 15 THEN @USerPctg
							WHEN @Level = 1 AND @UserCount BETWEEN 16 AND 25 THEN @USerPctg			
							
						END) PERCENT 
							ActivityDate, AlienFileLocation,BondAmount, BondNumber, UserID						
						FROM    
							PopulationBondsCash_Temp		
						ORDER BY 
							NEWID()
						SET @i = @i + 1 	
		END

Data Sample:

UserID	               UserCount	USerPctg
BENOIT III DONALD	1	        0.000224
ANSON-PRITCHARD BRENDA	352	        0.07919
KING LORRAINE	        512	        0.115185
DUBOIS RAYMOND	        351	        0.078965
BRIGANTE MATTHEW	456	        0.102587
ARMELL SHERRY	        538	        0.121034
ST. MARTIN DOMINIC	762	        0.171428
ROY SCOTT	        492	        0.110686
FRENETTE BERNARD	561	        0.126209
BRIGGS PEGGY	        420	        0.094488

evanburen
Posting Yak Master

167 Posts

Posted - 2011-10-19 : 16:16:58

Sorry, these two statements are incorrect

SET @UserCount = (SELECT MAX(UserCount) FROM PopulationBondsCashSummary)
SET @USerPctg = (SELECT MAX((CAST(USerPctg * 100 AS Int))) FROM PopulationBondsCashSummary)

I have them as

SET @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary)
SET @USerPctg = (SELECT (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary)

I was just experimenting with MAX but that's what I need.

evanburen
Posting Yak Master

167 Posts

Posted - 2011-10-20 : 08:02:22

Any suggestions with this one?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources