asked on

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

ajcheung78

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)

ajcheung78

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

evanburen

ASKER

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

ajcheung78

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) )

ajcheung78

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

evanburen

ASKER

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

ajcheung78

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

evanburen

ASKER

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

ajcheung78

Can you provide some sample data and the expected results?

evanburen

ASKER

Cash sample.png above contains the data in the table PopulationBondsCashSummary.

evanburen

ASKER

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.

ASKER CERTIFIED SOLUTION

ajcheung78

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

evanburen

ASKER

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

evanburen

ASKER

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