Conditionally Incrementing a Counter in a While Loop
-
Tim,
It's to populate a jury selection pool. I have a table full of licensed drivers (RandomEvents), and we need a random selection. So once and only once, I need to assign them a random number. In the first temp table (@RandomNewID), I assigned a newid to each driver, and in the second temp table (@RandomNumber), I ordered by that newid to give them their random number. So if I have 27 rows, I have entries numbered 1 through 27 which are now assignable.
Not content to consider that random enough, my user prefers not to use the first 10 entries as his jury pool. Instead, he wants the entries assigned in an nth row pattern. So the first time through the assignment process, I compute that I need every other entry selected to assign 10 of the 27. So that's 2,4,6,8,10,12,14,16,18,20.
Then he decides he really needs 15, or 5 more. The records I have available for assignment are 1,3,5,7,9,11,13,15,17,19,21,22,23,24,25,26,27.
(We're using the random numbers assigned in the first run, which is why @RandomAssigned is used. It contains either the contents of @RandomNumber or the permanent table, RandomEvents. I suppose I could write everything back to RandomEvents the first time, and then just use that to populate @RandomAssigned.)
Because I have 17 left, I can get the next 5 by reading every third record. So that should be 5,11,17,22,25.
It's this second time through that means I have to rely on a row counter instead of the random number. Now as luck would have it, I can get my five jurors by looking for random numbers divisible by 3 (3,9,15,21,24). But suppose he were looking for 8 more jurors instead of 5. My interval would be 2, but the only random numbers I have left that are divisible by 2 are 22,24,26, which isn't enough.
As for being overcomplicated, I couldn't agree with you more.
Mattie
-
"Not content to consider that random enough, my user prefers not to use the first 10"
Hmm since you have RANDOM numbers there is NO sense to go and pick on Nth row pattern.
The solution that I gave you DOESN T reiterate. It works only for first situation!!!!!! on second step IT WILL FAIL since I don t test if the columns was assigned allready or not.
PS: RANDOM IS RANDOM even if you choose to pick again randomly or ordered from an RANDOM ORDERED SET
Kindest Regards,Vasc
-
Vasc,
I had that discussion. I lost.
I'm not sure that your solution won't work for more than one run, because my process only brings over the rows without an assignment date. But if it's counting on the fact that there will be enough RandomNumbers to fit the pattern, then it will fail.
And give absolutely no thought to the fact that I need an audit trail to prove that the numbers were assigned randomly. To the extent that's possible, I'm doing that by numbering sequentially (by one), and then using the datetime stamp to show in which run they were selected.
Aren't you glad you asked?
Mattie
If it was easy, anyone could do it.
-
SET @@Rowcount = @NumberToSelect
Select IDENTITY(int, 1,1) as RandomID,
PersonId
INTO #Selected
FROM dbo.Candidates
WHERE
ORDER BY NEWID()
SET @@Rowcount = 0
Than you can join #Selected to original table by Person ID and do whatever you want with this list.
_____________
Code for TallyGenerator -
I m affraid they are asking you to pick 1 by 1 in a roll and retain the roll time... not to pick 5 in 1 roll (1 datetime) but to pick 5 in 5 rolls (5 datetimes ) wich will lead to a cicle
the iteration with a step just doesn t make sense...
Kindest Regards,Vasc
-
Does the choice of second candidate depend on the personality of the first chosen?
If not, you may choose all in one roll and then work out selected list in a way to fit your way of thinking.
_____________
Code for TallyGenerator -
Well, I've come up with a set-based solution.
What inspired this was Sergiy's post that made me realize I could have a rownumber independent from the assigned random number in the permanent table. I also found deleting the records from the temp table that I knew I wouldn't need worked better than using set rowcount. This gives me the exact same results as I got using the while loop. So here's my new solution, with the changes in green:
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @howmany int
DECLARE @WhichCourt char(4)
SET @HowMany = 500
SET @WhichCourt = '4590'
DECLARE @RecordsUpdated int
DECLARE @DateAssigned datetime
DECLARE @Interval int
DECLARE @CourtUnassignedCount int
DECLARE @CourtNumberedCount int
DECLARE @RandomNewID TABLE
(
UID int,
RandomNewID uniqueidentifier
)
DECLARE @RandomNumber TABLE
(
UID int,
RandomNumber int IDENTITY (1, 1) NOT NULL,
RandomNewID uniqueidentifier
)
DECLARE @RandomAssigned TABLE
(
UID int,
RandomNumber int,
DateAssigned datetime,
RowNumber int PRIMARY KEY IDENTITY (1,1) NOT NULL
)
SET @RecordsUpdated = 0
SET @DateAssigned = getdate()
SET @Interval = 0
SELECT @CourtUnassignedCount = Count(*)
FROM RandomEvents
WHERE @WhichCourt = Court
AND DateAssigned IS NULL
SELECT @CourtNumberedCount = Count(*)
FROM RandomEvents
WHERE @WhichCourt = Court
AND RandomNumber IS NOT NULL
--Compute the highest nth interval based on how many are available and how many you want
IF @HowMany > 0
BEGIN
SELECT @Interval = Round(@CourtUnassignedCount/(@HowMany), 0, 1)
END
IF @Interval = 0
BEGIN
SET @Interval = 1
END
IF @CourtNumberedCount = 0 --we need to assign random numbers before we can continue
BEGIN
--give every record a unique identifier
INSERT INTO @RandomNewID (UID, RandomNewID)
SELECT UID, newid()
FROM RandomEvents
WHERE RandomNumber IS NULL
AND Court = @WhichCourt
--give every record a sequence number based on the unique identifier
INSERT INTO @RandomNumber
SELECT UID,
RandomNewID
FROM @RandomNewID
ORDER BY RandomNewID
--tell the records in the permanent table what their sequence number is
UPDATE RandomEvents
SET RandomNumber = r.RandomNumber
FROM RandomEvents re
INNER JOIN @RandomNumber r
ON re.UID = r.UID
END
IF @CourtNumberedCount = 0 --we need to use the temp table to get the random number
BEGIN
INSERT INTO @RandomAssigned (UID, RandomNumber)
SELECT UID,
RandomNumber
FROM @RandomNumber
ORDER BY RandomNumber
END
ELSE --we need to use the random numbers in the permanent table
BEGIN
INSERT INTO @RandomAssigned
SELECT UID,
RandomNumber,
DateAssigned
FROM RandomEvents
WHERE Court = @WhichCourt
AND DateAssigned IS NULL
ORDER BY RandomNumber
END
DELETE
FROM @RandomAssigned
WHERE RowNumber > @Interval * @howmany
UPDATE RandomEvents
SET DateAssigned = @DateAssigned,
@RecordsUpdated = @RecordsUpdated + 1
FROM RandomEvents re
INNER JOIN @RandomAssigned ra
ON re.UID = ra.UID
WHERE ra.RowNumber % @Interval = 0
While I am dealing with a small test table, replacing the previous solution with this one reduced the run times by an order of 5.
I want to thank everyone who posted their thoughts on and solutions to this problem, working under the theory that if you throw enough spaghetti at a wall, some of it will stick.
Mattie
-
Read a couple replies here and they are all on the right track. You are a novice in SQL code so I will give you a REALLY simple solution. If you are expecting failed inserts then you will want a On Error routine in there.
That aside. At the top of your SP, where you declare your variables
Declare
@count int (Or bigint if needed)
After this set it to
@Count = 0
When you read in your records to your memory variable (presume: @RandomAssigned or any other memory table you are using that you wan to get the count from you would then (again after it is populated)
have the following statement (The memory table is an example; substitute with whatever table you want to increment)
SELECT @Count = count(*)
FROM @RandomAssigned -- Or any other table here
WHERE -Whatever WHERE clause you want.
THEN Where you begin your loop to work on the records you would start it with
WHILE @Count > 0
BEGIN
-- Your logic in here.
-- At the end of your logic, where you want to get the next record add in:
SET @Count = (@Count - 1)
CONTINUE
END
-- The above three lines will cause a loop until @Count = 0
ALSO, after the WHILE xxx, BEGIN above, the first statement you PROBABLY want after that is to pick your first record SELECT @Variable = WhateverVariable, ect...
FROM xxx
Hope you get the idea. Let me (everyone) know if you still need help.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply