Looping through records

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 UserCount FROM PopulationBondsCashSummary)
                        --PRINT @UserCount;      
      
                        DECLARE @USerPctg Dec
                        SET @USerPctg = (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary)                        
                        --PRINT @UserPctg      
                        
                        SELECT  UserID, UserCount, USerPctg
                        FROM    PopulationBondsCashSummary

                                    SELECT TOP (CASE  
                        --            --Level 1
                                          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            
Table sample
evanburenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ajcheung78Commented:
Three things I see based on the way you have structured this.

First, looks like you need to store a variable that has the userid:

DECLARE @UserID VarChar(50)
 @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary WHERE rownum = @i)

Second, modify your query for setting the @UserCount as follows:

SET @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary WHERE rownum = @i)

Thirrd, modify your query for setting the @USerPctg as follows to use a windowing function and the userid:

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

0
ajcheung78Commented:
Correction on last statement - should be something like this.

SET @USerPctg = SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary WHERE UserID = @UserID) AS x GROUP by x.UserID
0
evanburenAuthor Commented:
Hi

I'm seeing an error 'Incorrect syntax near the keyword 'SELECT'. on this line:

SET @USerPctg = SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary WHERE UserID = 5) AS x GROUP by x.UserID       

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
                  
                        DECLARE @UserID VarChar(50)
                      SET @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary WHERE rownum = @i)
      
                        DECLARE @USerPctg Dec
                        SET @USerPctg = SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) FROM PopulationBondsCashSummary WHERE UserID = 5) AS x GROUP by x.UserID                         
                        --PRINT @UserPctg      
                        
                        SELECT  UserID, UserCount, USerPctg
                        FROM    PopulationBondsCashSummary

                                    SELECT TOP (CASE  
                        --            --Level 1
                                          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            
            

            
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

ajcheung78Commented:
Sorry - try this:

SET @USerPctg = (SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID) )
0
ajcheung78Commented:
Ok one more time:

SET @USerPctg = (SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID) ) x
0
evanburenAuthor Commented:
Thanks, but still no luck.  Incorrect syntax near ')' on this line

SET @USerPctg = (SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID)) x
0
ajcheung78Commented:
I guess I am not a very good visual debugger - try this:

SET @USerPctg = SELECT SUM(x.USerPctg) FROM (SELECT TOP 5 (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID)) x
0
evanburenAuthor Commented:
I appreciate the help and it's not erroring out on the same line any longer but the overall query still isn't producing what I need.  This is what I have now.

My end goal is to produce a select statement like this but substituting the TOP percent based on the number of records for each userID and the
@userPctg value I'm pulling from another table.  Maybe my code needs a complete rewrite.  Thanks


SELECT TOP 10 PERCENT
ActivityDate, AlienFileLocation,BondAmount, BondNumber, UserID                                    
FROM    
PopulationBondsCash_Temp            
ORDER BY
NEWID()

            
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 <= @numrows)
		BEGIN
				DECLARE @UserCount Int
			
				DECLARE @UserID VarChar(50)
			    SET @UserCount = (SELECT UserCount FROM PopulationBondsCashSummary WHERE @numrows = @i)
	
				DECLARE @USerPctg Dec
				SET @USerPctg = (SELECT SUM(x.USerPctg) FROM (SELECT (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID) x)  				
					
				
				SELECT  UserID, UserCount, USerPctg
				FROM    PopulationBondsCashSummary
                
						SELECT TOP (CASE  
						--Level 1
							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
							Else
								100
						END) PERCENT 
							ActivityDate, AlienFileLocation,BondAmount, BondNumber, UserID						
						FROM    
							PopulationBondsCash_Temp		
						ORDER BY 
							NEWID()
							PRINT @UserID
						SET @i = @i + 1 	
		END

Open in new window

0
ajcheung78Commented:
Can you provide some sample data and the expected results?
0
evanburenAuthor Commented:
Cash sample.png above contains the data in the table PopulationBondsCashSummary.  Desired Results
0
evanburenAuthor Commented:
Here is the data in PopulationBondsCash_Temp.   The UserCount field doesn't necessarily have to be in the desired results.  The number of records for each UserID should be based on the TOP @UserPctg value from  PopulationBondsCashSummary.   Thanks again.


 PopulationBondsCash_Temp
0
ajcheung78Commented:
I apologize that I did not do this earlier but I have been simply trying to correct your syntax errors assuming that your logic is sound.  

Looking a bit closer I see that even with your examples I cannot understand what you are trying to accomplish and how you arrived at the numbers in the desired results.

For instance how are you getting 0.03125 for the UserPctg and why are there multiple records for each UserID.

Can you provide just the data for one full example - e.g. for the ANSON-PRITCHARD BRENDAN record in its entirety and also provide the business reasons for what you are doing.

Also, this snippet of code makes no sense to me - what are you trying to accomplish here:
SELECT TOP (CASE  
                        --            --Level 1
                                          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 

Open in new window


Based on what you have stated so far and your original comments I would rewrite your code to start as follows and then add the logic you need to get the desired results.

 
DECLARE @UserID VarChar(50)

DECLARE c_PopulationBondsCashSummary CURSOR FOR
SELECT UserID FROM PopulationBondsCashSummary

OPEN c_PopulationBondsCashSummary

FETCH NEXT FROM c_PopulationBondsCashSummary INTO @UserID

WHILE @@FETCH_STATUS = 0

BEGIN
	
	DECLARE @USerPctg Dec
	SET @USerPctg = (SELECT SUM(x.USerPctg) FROM (SELECT (CAST(USerPctg * 100 AS Int)) AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID) x)  

	-- NEED SOME LOGIC HERE	
	
	FETCH NEXT FROM c_PopulationBondsCashSummary INTO @UserID
	
END 

CLOSE c_PopulationBondsCashSummary
DEALLOCATE c_PopulationBondsCashSummary

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
evanburenAuthor Commented:
Thank you for your continued help. This is what I am trying to accomplish.  I need to get a
random sample (NEWID()) of records for each userID based on the total percentage of records in
PopulationBondsCash_Temp.  In the screenshot cashsample.png, KING LORRAINE has 512 records in
PopulationBondsCash_Temp which represent .115 precent of the records in that table.  
As a result, I need my random of sample of records for this userID to also be .115% of the total from
PopulationBondsCash_Temp.   PopulationBondsCashSummary is cashsample.png which is just used to hold
the percentage of records each user should be assigned.

So if the total number of records in PopulationBondsCash_Temp is 20, then I need to pull 8% of the
20 records for ANSON-PRITCHARD, 11% for KING LORRAINE, 8% for DUBOIS RAYMOND, 10% for BRIGANTE MATTHEW etc.

I hope this makes sense
0
evanburenAuthor Commented:
I was able to get it working with your cursor code.  Thanks!

DECLARE @UserID VarChar(500)

DECLARE @Level Int
SET @Level = 2

DECLARE @UserCount Int
SET @UserCount = (SELECT COUNT(*) FROM PopulationBondsCash_Temp)

DECLARE @PopSize Int
SET @PopSize = (
CASE
--Level 1
 WHEN @Level = 1 AND @UserCount BETWEEN 2 AND 8 THEN 2
 WHEN @Level = 1 AND @UserCount BETWEEN 9 AND 15 THEN 2
 WHEN @Level = 1 AND @UserCount BETWEEN 16 AND 25 THEN 3
 --Level 2
WHEN @Level = 2 AND @UserCount BETWEEN 2 AND 8 THEN 2
WHEN @Level = 2 AND @UserCount BETWEEN 9 AND 15 THEN 3
WHEN @Level = 2 AND @UserCount BETWEEN 16 AND 25 THEN 5
--Level 3
WHEN @Level = 3 AND @UserCount BETWEEN 2 AND 8 THEN 3
WHEN @Level = 3 AND @UserCount BETWEEN 9 AND 15 THEN 5
WHEN @Level = 3 AND @UserCount BETWEEN 16 AND 25 THEN 8
END
)

DECLARE @i int
SET @i = 1

DECLARE c_PopulationBondsCashSummary CURSOR FOR
SELECT UserID FROM PopulationBondsCashSummary

OPEN c_PopulationBondsCashSummary

FETCH NEXT FROM c_PopulationBondsCashSummary INTO @UserID

WHILE @@FETCH_STATUS = 0 AND @i <= @Popsize
BEGIN

	DECLARE @USerPctg Float
	SET @USerPctg =  (SELECT USerPctg AS USerPctg FROM PopulationBondsCashSummary WHERE UserID = @UserID)  
	    
    DECLARE @TOPPopSize Int
    SET @TOPPopSize = CAST(@Popsize * @USerPctg AS Int)
       
   SELECT  UserID, UserCount, USerPctg
   FROM    PopulationBondsCashSummary

                SELECT TOP (CASE  
                --Level 1
                    WHEN @Level = 1 AND @PopSize BETWEEN 2 AND 8 THEN @TOPPopSize
                    WHEN @Level = 1 AND @PopSize BETWEEN 9 AND 15 THEN @TOPPopSize
                    WHEN @Level = 1 AND @PopSize BETWEEN 16 AND 25 THEN @TOPPopSize                      
                    --Level 2
					WHEN @Level = 2 AND @PopSize BETWEEN 2 AND 8 THEN @TOPPopSize
					WHEN @Level = 2 AND @PopSize BETWEEN 9 AND 15 THEN @TOPPopSize
					WHEN @Level = 2 AND @PopSize BETWEEN 16 AND 25 THEN @TOPPopSize
					--Level 3
					WHEN @Level = 3 AND @PopSize BETWEEN 2 AND 8 THEN @TOPPopSize
					WHEN @Level = 3 AND @PopSize BETWEEN 9 AND 15 THEN @TOPPopSize
					WHEN @Level = 3 AND @PopSize BETWEEN 16 AND 25 THEN @TOPPopSize			 
                      
                END) 
                      ActivityDate, AlienFileLocation,BondAmount, BondNumber, UserID                                                   
                FROM    
                      PopulationBondsCash_Temp                       
                WHERE UserID = @UserID
				
                ORDER BY 
                      NEWID()

	FETCH NEXT FROM c_PopulationBondsCashSummary INTO @UserID
	SET @i = @i + 1	
END 

CLOSE c_PopulationBondsCashSummary
DEALLOCATE c_PopulationBondsCashSummary 

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.