July 29, 2003 at 11:02 am
Please help, my random assignment script gives me not so random assignments! Am I using my cursor wrongly? How about the random generator? Here is part of the code:
--loop through assignable cycles
declare cur_cycles cursor
keyset
for
SELECT CYC
FROM Austin..tbl_ManDial_CycOwners
WHERE (EffectiveDate <= dbo.fxn_TruncDate(GETDATE()))
AND (EndDate IS NULL)
GROUP BY CYC
ORDER BY CYC
open cur_cycles
fetch next from cur_cycles into @CYC
while(@@fetch_status=0)
begin
declare cur_randass cursor
keyset
forSELECTEmpNo
FROM austin.dbo.tbl_ManDial_Placements
WHERE EmpNo IS NULL
AND(CYC = @CYC)
ANDBal < 11500.00
ANDPlcmtDt = dbo.fxn_truncdate(getdate())
ORDER BY Rand(Checksum(NewID()))
open cur_randass
fetch next from cur_randass into @RandAss
while(@@fetch_status=0)begin
select @EmpNo =
(SELECT TOP 1EmpNo
FROM Austin.dbo.vw_ManDial_Staff v
WHERE CYC = @CYC
ORDER BY Rand(Checksum(NewID())))
UPDATE austin.dbo.tbl_ManDial_Placements
SET EmpNo = @EmpNo
WHERE CURRENT OF cur_randass
fetch next from cur_randass into @RandAss
end
close cur_randass
deallocate cur_randass
fetch next from cur_cycles into @CYC
end
close cur_cycles
deallocate cur_cycles
set nocount off
go
J. Moseley
[font="Courier New"]ZenDada[/font]
July 29, 2003 at 11:18 am
I'm really at a loss as to what exactly is going on in this script, but to get a randow ordering, just do ORDER BY NEWID(), not ORDER BY RAND(CHECKSUM(NEWID()))
July 30, 2003 at 2:07 pm
NEWID() is random enough, although I can't find any documentation on how "random" it is for statistical sampling. I have run some tests and haven't been able to find any pattern in the results. It will not repeat after 65K calls like RAND().
CHECKSUM() is probably not going to make NEWID() any more random, but not any less either. It's just a waste of CPU cycles.
RAND() is the killer. It is the simple 16-bit C function, so it will repeat every 65,536 cycles. You compound the error by giving it a seed value every time. This is a common mistake. Instead of making RAND() more random, it makes it much more likely that you will get repeated values. The seed parameter is not meant to make RAND() random, it is there so you can generate the same list of pseudo-random values repeatedly for some kind of Monte Carlo modelling.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply