Solved

# Tough SQL Challenge

Posted on 2011-10-13
200 Views
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()
0
Question by:evanburen

LVL 9

Expert Comment

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

LVL 8

Expert Comment

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
``````
0

LVL 41

Accepted Solution

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

Author Comment

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

Author Comment

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

## Featured Post

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
how to add IIS SMTP to handle application/Scanner relays into office 365.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…