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?
 
ralmadaConnect With a Mentor Commented:
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.