Tough SQL Challenge

My query grabs a random set of rows from a table based on the total number of rows in the table.  For example, if there are 12 rows in the table then I grab 2 random records from it using NEWID() for a level 1.  What I really need is to use the same logic but apply it to the total number of records for each UserID.   For example, if UserID has a total of 6 records then grab 2 random records for from that UserID group (instead of the total table).  This one has me stumped.  Thanks.

CREATE PROCEDURE [dbo].[p_CashSample]
      @Level Int = NULL,
    @RecordCount Int = NULL OUTPUT
AS
SET @RecordCount = (SELECT COUNT(*) FROM PopulationBondsCash_Temp)
FROM    
PopulationBondsCash_Temp
GROUP BY UserID

SELECT TOP
      (      
            CASE
                  --Level 1
                  WHEN @Level = 1 AND @RecordCount BETWEEN 2 AND 8 THEN 2
                  WHEN @Level = 1 AND @RecordCount BETWEEN 9 AND 15 THEN 2
                  WHEN @Level = 1 AND @RecordCount BETWEEN 16 AND 25 THEN 3
                  --Level 2
                  WHEN @Level = 2 AND @RecordCount BETWEEN 2 AND 8 THEN 2
                  WHEN @Level = 2 AND @RecordCount BETWEEN 9 AND 15 THEN 3
                  WHEN @Level = 2 AND @RecordCount BETWEEN 16 AND 25 THEN 5
                  --Level 3
                  WHEN @Level = 3 AND @RecordCount BETWEEN 2 AND 8 THEN 3
                  WHEN @Level = 3 AND @RecordCount BETWEEN 9 AND 15 THEN 5
                  WHEN @Level = 3 AND @RecordCount BETWEEN 16 AND 25 THEN 8
                  END
      )
            ActivityDate, AlienFileLocation, UserID, BondAmount, BondNumber, COUNT(UserID) OVER (Partition by UserID) AS UserCount
            FROM    
                  PopulationBondsCash_Temp            
            ORDER BY
                  NEWID()
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.

sshah254Commented:
I would just write a SP to create a temp table with the list of users; and then for each user dump the random data into another table and loop over all the users.

SS
0
CrashmanCommented:
you can use a cursor,
 DECLARE x CURSOR
        FOR SELECT UserID, COUNT(UserID) FROM
FROM    
PopulationBondsCash_Temp
GROUP BY UserID

OPEN x
        
FETCH NEXT FROM x INTO @ID, @RecordCount 

 WHILE @@FETCH_STATUS = 0
        BEGIN
--Do wherever you want...
print @ID
print @RecordCount
FETCH NEXT FROM x INTO @ID, @RecordCount 
end
CLOSE x
        DEALLOCATE x

Open in new window

0
ralmadaCommented:
you can just do it like this:
CREATE PROCEDURE [dbo].[p_CashSample]
      @Level Int = NULL,
    @RecordCount Int = NULL OUTPUT
AS 
SET @RecordCount = (SELECT COUNT(*) FROM PopulationBondsCash_Temp)
FROM    
PopulationBondsCash_Temp
GROUP BY UserID

select * 
from (
SELECT 	ActivityDate, 
	AlienFileLocation, 
	UserID, 
	BondAmount, 
	BondNumber, 
	COUNT(UserID) OVER (Partition by UserID) AS UserCount,
	row_number() over (partition by UserID order by newid()) rn
FROM     PopulationBondsCash_Temp            
) a
where rn <= (CASE
                  --Level 1
                  WHEN @Level = 1 AND @RecordCount BETWEEN 2 AND 8 THEN 2
                  WHEN @Level = 1 AND @RecordCount BETWEEN 9 AND 15 THEN 2
                  WHEN @Level = 1 AND @RecordCount BETWEEN 16 AND 25 THEN 3
                  --Level 2
                  WHEN @Level = 2 AND @RecordCount BETWEEN 2 AND 8 THEN 2
                  WHEN @Level = 2 AND @RecordCount BETWEEN 9 AND 15 THEN 3
                  WHEN @Level = 2 AND @RecordCount BETWEEN 16 AND 25 THEN 5
                  --Level 3
                  WHEN @Level = 3 AND @RecordCount BETWEEN 2 AND 8 THEN 3
                  WHEN @Level = 3 AND @RecordCount BETWEEN 9 AND 15 THEN 5
                  WHEN @Level = 3 AND @RecordCount BETWEEN 16 AND 25 THEN 8
                  END)

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 everyone.  This worked

CREATE PROCEDURE [dbo].[p_CashSample4]
      @Level Int = NULL,
      @RecordCount Int = NULL OUTPUT
AS

SELECT  @RecordCount = COUNT(*)
FROM    PopulationBondsCash_Temp

select *
from (
SELECT       ActivityDate,
      AlienFileLocation,
      UserID,
      BondAmount,
      BondNumber,
      COUNT(UserID) OVER (Partition by UserID) AS UserCount,
      row_number() over (partition by UserID order by newid()) rn
FROM     PopulationBondsCash_Temp            
) a
where rn <= (CASE
                  --Level 1
      WHEN @Level = 1 AND @RecordCount BETWEEN 2 AND 8 THEN 2
      WHEN @Level = 1 AND @RecordCount BETWEEN 9 AND 15 THEN 2
      WHEN @Level = 1 AND @RecordCount BETWEEN 16 AND 25 THEN 3
      --Level 2
      WHEN @Level = 2 AND @RecordCount BETWEEN 2 AND 8 THEN 2
      WHEN @Level = 2 AND @RecordCount BETWEEN 9 AND 15 THEN 3
      WHEN @Level = 2 AND @RecordCount BETWEEN 16 AND 25 THEN 5
      --Level 3
      WHEN @Level = 3 AND @RecordCount BETWEEN 2 AND 8 THEN 3
      WHEN @Level = 3 AND @RecordCount BETWEEN 9 AND 15 THEN 5
      WHEN @Level = 3 AND @RecordCount BETWEEN 16 AND 25 THEN 8
      ELSE
                100
  END)
0
evanburenAuthor Commented:
Sorry, actually what this does is produce the total sample size for each user.  For example, if the recordcount = 16, then this code produces 16 records for every UserID.  What I really need is for the total sample size to be 16 and then divided evenly among each of the users so if there were 4 users there would we 4 records per user.  Sorry for the confusion.
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.