December 15, 2004 at 12:20 pm
Incorrect syntax near the keyword 'Select'.
DECLARE @SQL NVARCHAR(500)
SET @SQL = N'SET @RandomisedNumber = Select top 1 PkNumber, PkNumber % 3 as mod from dbo.Numbers order by Newid()'
EXEC sp_executesql @SQL, N'@RandomisedNumber decimal OUTPUT' ,@RandomisedNumber OUTPUT
Case
Else Cast(Form as Int) +1
Case
Else Cast(Form as Int) -1
December 15, 2004 at 12:41 pm
DECLARE @SQL NVARCHAR(500)
SET @SQL = N'SET @RandomisedNumber = (rand() * 2)'
Case
Else Cast(Form as Int) +1
Case
Else Cast(Form as Int) -1
December 15, 2004 at 1:03 pm
All my method does is generate new random numbers everytime the query is run. But if you run this query as a subquery then it is run 1 time for each record in the set (by using select top 1 instead of top 10)... meaning you get a new rand for each line... then you can do whatever you want to that number (didn't read the whole logic you are trying to accomplish after that).
exemple (note the "where O.id = O.id" in the subquery to force sql server to rerun the query at each line)
Select O.Name, (Select top 1 PkNumber from dbo.Numbers where O.id = O.id order by Newid()) as RandNum from dbo.SysObjects O where O.XType = 'U' order by O.Name
December 15, 2004 at 8:04 pm
I'm going to repost this solution because a) the other solutions are still using row-based logic and, even worse, dynamic SQL for every row; and b) I don't think you understood it.
"stable hands is different between owners" Isn't StableHands a field in ViewWeeklyFormUpdate? This code is using the correct StableHands value for each horse. By the way, if StableHands only goes to 10 why are you dividing it by 22? Int(StableHands)/22 will always return 0 for StableHands < 22. In VB, "/" is always a floating-point operation (so Round(Int(StableHands)/22) = 1 for StableHands > 10). In T-SQL, "/" is an integer operation (like VB "\") if both the numerator and denominator are integer types. I've adjusted the numbers in the T-SQL code to allow for this.
"want a diff rand for each horse" - that's what NEWID() does, it generates a unique, random number for each row. But think in sets, you want to randomly assign each horse to one of three groups. Selecting a random group of 1/3 of the horses is the same thing, you don't care what the actual random value is for each horse.
-- Fewer than 11 stablehands: 1/3 decrement (1 min), 1/3 increment (5 max)
-- 11-32 stablehands: 2/3 increment (5 max)
-- 33+ stablehands: all increment (5 max)
create table #decrement (HorseID int not null primary key clustered)
-- Determine which horses will be decremented
-- 33% of (StableHands < 11)
insert into #decrement
select top 33 percent HorseID
from ViewWeeklyFormUpdate
where StableHands < 11
order by newid()
update ViewWeeklyFormUpdate set Form = Form - 1
where Form > 1 and HorseID in (select HorseID from #decrement)
-- Determine which horses will be incremented
-- 50% of (StableHands < 11) that were not decremented
-- 67% of (11 <= StableHands < 33)
-- All StableHands >= 33
update ViewWeeklyFormUpdate set Form = Form + 1
where Form < 5 and (StableHands >= 33 or HorseID in (
select top 50 percent HorseID from ViewWeeklyFormUpdate
where StableHands < 11 and HorseID not in (select HorseID from #decrement)
order by newid()
union all
select top 67 percent HorseID from ViewWeeklyFormUpdate
where (StableHands between 11 and 32)
order by newid()))
drop table #decrement
December 16, 2004 at 1:23 am
December 20, 2004 at 12:25 pm
hmmm no replies?
December 22, 2004 at 9:44 am
I've been too busy to read my email lately, but I'll take another shot at enlightening you.
First, the problem with the formula "IntegerField / 22" in T-SQL is you will have a truncated integer result, with no decimals and no rounding. The answer is 0 for values 0-21, 1 for 22-43, etc. If you need to do this use "IntegerField / 22.0" to force a decimal result.
Second, you seem to be determined to use this formula row-by-row just like you do in Visual Basic. To coin a phrase, "It doesn't look like we're in Visual Basic anymore, Toto!". Try to find a set-based solution.
You're using Int(StableHands / 22) to break all the horses into different groups that have different probablilities of being incremented/decremented. Now maybe between all your Int() and Round() functions I got confused about the boundary between groups and the exact probabilities (Is it 1-10; 11+ stablehands or 1-21;22+? Is it 33%/33%/33% or 16%/33%/50%?) but the structure of the solution is correct.
January 11, 2005 at 10:47 am
Declare @LstHrse as int
set @i = 0
set @LstHrse = 'Select Top 1 HorseID From TblHorses Order by HorseID Desc'
while @i < @LstHrse
begin
Case
Else Cast(Form as Int) +1
Case
Else Cast(Form as Int) -1
end
January 11, 2005 at 11:27 am
You can't do that
Where HorseID = @LstHrse
end
means
Where HorseID = 'what ever you typed here but in varchar format'.
January 11, 2005 at 11:40 am
January 11, 2005 at 1:04 pm
Yes we can all see that... hopefully you'll make it work in a set based operation someday.
Good luck.
January 12, 2005 at 7:46 am
hmm trouble is each horse is different and needs a different form change. I can't see how to make it set based
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply