Help - New to T-SQL
- Hi I am new to T-SQL and can do stuff in asp but now need to use jobs to change data at set times, the following code is what i have in vb, any pointers in how to make it work in T-SQL would be appreciated
thanks
Dagaz
Code------>
Randomize()
Set RSFormUpd = Server.CreateObject("ADODB.Recordset")
RSFormUpd.open "Select * From ViewWeeklyFormUpdate", Conn, 3, 3
Do While Not RSFormUpd.EOF
UpdForm = Round((int(RSFormUpd("StableHands"))/22) + (rnd * 2),0)
If UpdForm = 0 Then
If int(RSFormUpd("Form")) - 1 = 0 Then
RSFormUpd("Form") = int(RSFormUpd("Form"))
Else
RSFormUpd("Form") = int(RSFormUpd("Form")) - 1
End If
End If
If UpdForm = 1 Then
RSFormUpd("Form") = int(RSFormUpd("Form"))
End If
If UpdForm = 2 Then
If int(RSFormUpd("Form")) + 1 = 6 Then
RSFormUpd("Form") = int(RSFormUpd("Form"))
Else
RSFormUpd("Form") = int(RSFormUpd("Form")) + 1
End If
End If
Response.write RSFormUpd("Form") & " "
RSFormUpd.update
RSFormUpd.movenext
Loop
RSFormUpd.close
Set RSFormUpd = Nothing
in pseudo code
have a view which is a combination of TblHorses and TblOwners has fields:Form from Horses
StableHands from owners
linked by Horses.OwnedBy = Owners.OwnerID
ok goes through all the horses and creates a number either 0,1,2
based on the formula
Round((int(RSFormUpd("StableHands"))/22) + (rnd * 2),0)
then if the random number = 0 the Form goes down 1 unless is already 1 then stays the same
if = 1 then stays same
if = 2 then form goes up 1 unless it is already 5 then it stays the same
many thanks in advanceDagaz -
Put your stuff in a stored procedure which shouldn't be too difficult at all. Create then a job and schedule it for your needs.
And, btw, you should really use the ADO command object.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] - thank u, u are a god send.Silly q now. Don't suppose you could do a simple example? if too much to ask dont worry.But thanks anyways at least i have a starting point
-
Something like the following should work:
Create Procedure usp_my_procedure As
Update ViewWeeklyFormUpdate
Set Form =
Case
You may need to replace the view with it's constituent parts.
Regards,
Nic Washington
-
Don't suppose you could do a simple example?
I could, but Nic has done so already.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] - Thank you, once i see something layed out like that it makes so much sense and a lot easier to understand.When I get home tonight will give it a go.Dagaz
-
lol ok got home and definate improvement. my only question is that rand seems to be the same rand for all records? am i wrong in this or is there another setting i need to change
- ooooo have modified a bit: seems to work minus every record ending up with same result lol:Update ViewWeeklyFormUpdateSet Form =Case round( cast( StableHands as int)/22 + (rand() * 2),0)When 1 Then Cast(Form as Int)When 2 Then
Case
When (Form+1)=6 Then Cast(Form as Int)Else Cast(Form as Int) +1
EndElseCase
When (Form-1)=0 Then Cast(Form as Int)Else Cast(Form as Int) -1
EndEnd -
The RAND function only executes once per query...no matter how you write it all rows will have the same result.
The common way to randomize in T-SQL is to use the NEWID() function which generates a unique random value for each row. Order by this and select the desired count or percent off the top.
When converting row-by-row procedural logic to T-SQL you have to change your approach to set-based rather than row-based.
Your problem involves different probabilities for different groups, so there are multiple random selections in my solution:
-- Fewer than 22 stablehands: 1/3 decrement (1 min), 1/3 increment (5 max)
-- 22-43 stablehands: 2/3 increment (5 max)
-- 44+ stablehands: all increment (5 max)
create table #decrement (HorseID int not null primary key clustered)
-- Determine which horses will be decremented
-- 33% of (StableHands < 22)
insert into #decrement
select top 33 percent HorseID
from ViewWeeklyFormUpdate
where StableHands < 22
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 < 22) that were not decremented
-- 67% of (22 <= StableHands < 44)
-- All StableHands >= 44
update ViewWeeklyFormUpdate set Form = Form + 1
where Form < 5 and (StableHands >= 44 or HorseID in (
select top 50 percent HorseID from ViewWeeklyFormUpdate
where StableHands < 22 and HorseID not in (select HorseID from #decrement)
order by newid()
union all
select top 67 percent HorseID from ViewWeeklyFormUpdate
where (StableHands between 22 and 43)
order by newid()))
drop
table #decrement
-
Hi Guys,
I know there will be loads of abuse for this but had to do it.........
You can make rand work on multiple occaisions in one sp - by using a bit of dynamic sql
and sp_executesql e.g.:-DECLARE @RandomisedNumber decimal,
@SQL nvarchar(4000),
@Counter decimal
SET @Counter = 1
WHILE @Counter < 10
BEGIN
SET @sql = N'SET @RandomisedNumber = round(' + CAST(@Counter AS Varchar(255)) + '/22 + (rand() * 2),0)'
EXEC sp_executesql @sql, N'@RandomisedNumber decimal OUTPUT', @RandomisedNumber OUTPUT
PRINT CAST(@RandomisedNumber as varchar(255))
SET @Counter = @Counter + 1
END
Have fun
Steve
We need men who can dream of things that never were.
- Hmm any chance showing inside my example? as i have tried the below but still using same randDECLARE @RandomisedNumber decimal
DECLARE @sql NVARCHAR(500)
SET @sql = N'SET @RandomisedNumber = (rand() * 2)'
EXEC sp_executesql @sql, N'@RandomisedNumber decimal OUTPUT' ,@RandomisedNumber OUTPUT
Update ViewWeeklyFormUpdateSet Form =Case round( cast( StableHands as int)/22 + CAST(@RandomisedNumber as varchar(255)),0)When 1 Then Cast(Form as Int)When 2 ThenCase
When (Form+1)=6 Then Cast(Form as Int)Else Cast(Form as Int) +1
EndElseCase
When (Form-1)=0 Then Cast(Form as Int)Else Cast(Form as Int) -1
EndEndand i like the idea of guid but the stable hands is different between owners each owner can have between 0 and 10 stable hands and want a diff rand for each horse. -
Here's an idea...
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @i as int
set @i = 0
while @i < 8000 -- stop whenever you want but the bigger this table is the longer it'll take to rurn queries from it.
begin
Insert into dbo.Numbers Default Values
set @i = @i + 1
end
set @i = 0
while @i < 5
begin
Select top 10 PkNumber, PkNumber % 2 as mod from dbo.Numbers order by Newid()
set @i = @i + 1
end
If you run this query as a subquery you'll get a different rand everytime.
-
Hi,
Example with your existing code:-
Update ViewWeeklyFormUpdate
Declare @sql nvarchar(4000),
@RandomisedNumber decimal
Set Form =
SET @sql = N'SET @RandomisedNumber = rand() * 2'
EXEC sp_executesql @sql, N'@RandomisedNumber decimal OUTPUT', @RandomisedNumber OUTPUT
Case round( cast( StableHands as int)/22 + CAST(@RandomisedNumber as varchar(255)),0)
When 1 Then Cast(Form as Int)
When 2 Then
Case
When (Form+1)=6 Then Cast(Form as Int)
Else Cast(Form as Int) +1
End
Else
Case
When (Form-1)=0 Then Cast(Form as Int)
Else Cast(Form as Int) -1
End
End
Hope it helps.
Have fun
Steve
We need men who can dream of things that never were.
- cool and that means a different random number for each record?Thanks again all of you, is so nice to be on a forum where ppl help.Dagaz
- ok tried the above and got errors:Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'Declare'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '='.
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'Case'.
so moved the declares and that fixed the 1st 2 but can't get rid of the rest.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply