Need alernative to cursor

Hey guys and gals - here's my current code

DECLARE @.uID varchar(12)

DECLARE MyCursor CURSOR FOR
SELECT unique_identifier
FROM gvAbsence

OPEN MyCursor

FETCH NEXT FROM MyCursor
INTO @.uID

WHILE @.@.FETCH_STATUS = 0 BEGIN
UPDATE gvAbsence
SET holiday_brought_forward = CASE WHEN years_of_service < 5 THEN Floor(Rand() * 6) ELSE Floor(Rand() * 5) END
WHERE unique_identifier = @.uID

FETCH NEXT FROM MyCursor
INTO @.uID
END

CLOSE MyCursor
DEALLOCATE MyCursor

SELECT *
FROM gvAbsence

Which as you can see, sucks.
It cursors through nearly 4K records assigning a new random number (0-5) to an employees holiday_brought_forward. Naturally, this is an absolute ballache... I just can't think of a better solution at current so any advice you can give would be greatly appreciated :cool:

To put it simply, I want to assign a random number between 0 and 5 (inclusive) to each record in the table gvAbsence

-GeorgeWhich bit you stuck with? Version of SS?
ss5k method of getting a random number between 0 and 5. Not sure if it is the best way of course. With a suitable bit of SQLery I think you can get a random number per person.

select *
from
(select number
, rn = ROW_NUMBER() OVER (ORDER BY newid())
from dbo.numbers
where number between 1 and 5) AS der_t
where rn = 1

Did you try googling?|||SS 2K.
And the above code works how I want it to, but I was wondering if there was a better alternative to using a cursor?

--Return random number between 0 and 5 inc
SELECT Floor(Rand() * 6)

(Can Rand() return 1?)

I've just progressed on with my problem with the cursor and all is well, apart from it taking a full minute to cursor through and apply other updates etc.|||Define random for me:
Does the code need to generate a different number for a particular member of staf each time it is run? Does it need to generate an even distribution for the 4k records? Basically - do you need a truly random number?|||I am creating a test environment and to replicate the scenario I needed to assign all sorts of different values to certain fields (in this case holiday_bought_forward) to that I could test a bunch of different permeatations of the data.

It does not need to do it every time the code is run, except when I need to reset the data (and during testing the data will need reset a good number of times).

No, does not need to have an even distribution across the 4K records, truly random will do just fine ;)

It is not a high priority issue as it's only for the test environment but speeding things up a bit would be nice :p

I don't like cursors all that much (you guys have always told me not to use them!) so I was looking into what alternatives exist

Thanks Poots|||Does gvAbsence have a primary key? If so, extract them into a 2 column table, and when you extract them use the RAND function to generate your random value.

Since the RAND function returns a random float value between 0 and one, you will have to multiply by 5 and take the integer value (lookup FLOOR in BOL)

Hopefully this will get you started.

EDIT: Once you have this table, you can update gvAbsence with the random value from the table for a quick reset that will restore identical values each and every time you need a reset.|||My understanding was that rand won't work in a set based query.|||If I was to keep my cursor, I could use something like a counter instead of a random number for consistancy.
Something like

DECLARE @.Counter int
SET @.Counter = 0

UPDATE gvAbsence
SET holiday_brought_forward = @.Counter % 5
WHERE unique_identifier = @.uID

SET @. Counter = Counter + 1

I dunno, I still don't like cursoring...|||Aha! So nothing like random at all!

This is much easier now - we are deteriministic. Your people got a surrogate key (I know you like em ;))? Why not perform mod on that instead of an increment? Easy & set based.|||One issue I do have now is that the holiday_bought_forward has some constraints; after 5 years of service you're entitled to an extra days holiday -this means you can only bring over maximum of 4 days. (basically a total of 30).

I've actually finished my script now, but I'm simply not happy with the cursor. Like I said - this is only test data that I'm creating to replicate live stuff - so it doesn't have to be the same every time - it simply has to contain permeatations of every possibility (which it does).

If anyone's interested in seeing the redicuous script I had to write for this, just let me know :p|||Let's create some test data

select 'unique_identifier'=id, 'years_of_service'=id, 'holiday_brought_forward'=0
into #gvAbsence from sysobjects where id<20

Instead of using the the rand() function that does not work as expected use a numbers table e.g.

select top 6 seqno=identity(int) into #numbers from syscolumns

And instead of hard coding data into your code lets use a holiday_constraints table.
Now if the conditions chance you don't have to update code, just the data in your table.

create table #holiday_constraints (maxdays int, yrlow int, yrhigh int)
insert into #holiday_constraints select
6,0,5 union all select
5,6,999999999

Then the update.
Note: An unnecessary where clause in the sub select to force execution for every row.

update #gvAbsence
SET holiday_brought_forward =
(select top 1 seqno from #numbers c where seqno<=b.maxdays and a.unique_identifier=a.unique_identifier order by newid())
from #gvAbsence a, #holiday_constraints b
where a.years_of_service between b.yrlow and b.yrhigh

select * from #gvAbsence|||Unfortunately I'm using SQL Server 2000; TOP won't work.|||Also, there are more rules involved and the added problem of generating a random number between -5 and a conditional top value
I'm thinking that in this case a cursor might be my only solution.

I apologise, I don't think I made my problem/question very clear from the beginning but thanks everyone for your help! My final mark up of the cursor is below - the really clever line is the SET ;) Lovely formula right thar!

DECLARE MyCursor CURSOR FOR
SELECT unique_identifier
,holiday_entitlement
,holiday_brought_forward
FROM gvAbsence

OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @.uID, @.Ent, @.Bf

SET @.Min = -5
WHILE @.@.FETCH_STATUS = 0 BEGIN
SET @.Max = CASE @.Ent + @.Bf
WHEN 30 THEN 0
WHEN 29 THEN 1
WHEN 28 THEN 2
WHEN 27 THEN 3
WHEN 26 THEN 4
WHEN 25 THEN 5 END
UPDATE gvAbsence
SET holiday_bought = Floor(Rand() * (@.Max - @.Min + 1)) + @.Min
WHERE unique_identifier = @.uID

FETCH NEXT FROM MyCursor
INTO @.uID, @.Ent, @.Bf
END
CLOSE MyCursor
DEALLOCATE MyCursor|||Unfortunately I'm using SQL Server 2000; TOP won't work.Howdya figure that?|||I didn't think TOP was implemented in 2000?
Any time I try run a TOP statement in QA I get

SELECT TOP 5 surname
FROM employee
ORDER BY surname ASC
--
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '5'.

Errors!|||From SQL 2K BoL:
Limiting Result Sets Using TOP and PERCENT

The TOP clause limits the number of rows returned in the result set.
TOP n [PERCENT]
n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return:
TOP 120 /*Return the top 120 rows of the result set. */TOP 15 PERCENT /* Return the top 15% of the result set. */.If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned.

The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:

The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is terminated when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.
The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.The top keyword changed in 2005 only in so far as you can use a variable.|||Then how come I can't execute SELECT TOP statements? :confused:|||Have you checked the compatability level of the database?|||Works for me

USE Northwind
GO

SELECT TOP 5 LastName
FROM employees
ORDER BY LastName ASC
GO|||Ummm...assuming your "algorithym" is correct, why couldn't you just do

USE Northwind
GO

CREATE TABLE myTable99(unique_identifier int, years_of_service int, holiday_brought_forward int)
GO

INSERT INTO myTable99(unique_identifier, years_of_service)
SELECT 1, 1 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 3, 20 UNION ALL
SELECT 4, 30
GO

UPDATE g
SET holiday_brought_forward =
CASE WHEN years_of_service < 5 THEN Floor(Rand() * 6)
ELSE Floor(Rand() * 5) END
FROM myTable99 g
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||... because rand() will return the same value for all rows.:)|||Not necessarily...

select case floor (rand()*5)
when 1 then 1
when 2 then 2
when 3 then 3
when 4 then 4
else 5 end
from [table of your choice]

This came up in a very nice SQL puzzle posted here some time ago. For those of you who are interested...

select case floor (rand()*5)
when 1 then '1'
when 2 then '2'
when 3 then '3'
when 4 then '4'
when 5 then '5'
else 'OMG WTF LOL!!!111eleven!!' end
from [table of your choice]

EDIT: Due to the nature of the "oddity", it may not be a truly random sampling. Sounds like it may be random enough for you, though.|||Well pummel my piles with a paddle. What is that about then? The case statement causes the rand to be re-evaluated for each row rather than once for the set? And what's the deal with 'WTF'? I mean - wtf? You got a link?|||Actually, rand() is invoked on each row of the output. This is why inline user defined functions can get you in to piles of problems that will pummel your performance paddle(s) past pertinent perturbation.

Sadly, I do not have a link to the original puzzle. It may have been lost in the troubles that DBForums was going through some time back when posts just up and disappeared. Besides, it will likely be a good exercise for you to figure out what is going on in there.

DOH! Should have checked first. I guess it is the case statement mucking around with things, after all.|||Oh noblet - I forgot about the 0 :rolleyes:

But anyway:

select case 1
when 1 then floor (rand()*5) end
from sys.databases

select case 1
when 2 then NULL else floor (rand()*5) end
fromsys.databases

select case floor (rand()*5)
when -1 then NULL else floor (rand()*5) end
from sys.databases

Coooooooooooooooo.|||DOH! Should have checked first. I guess it is the case statement mucking around with things, after all.Ah jolly good! I was wondering what subtle distinction you were making :)|||tsk tsk tsk. Perhaps I should have taken the 0 into consideration, as well...

select case floor (rand()*5)
when 0 then '0'
when 1 then '1'
when 2 then '2'
when 3 then '3'
when 4 then '4'
when 5 then '5'
else 'OMG WTF LOL!!!111eleven!!' end
from [table of your choice]

Extend as necessary to convince yourself this is not so easy as all that.|||neat trick! this seems to work as well without the need for laying out all the possibilities in the case:

select case when name is not null then rand() else NULL end from sys.objects

all you need is something to check in the when clause that will always be true, but that the optimizer won't optimize away. 1=1 won't work. That is, this is broken:

select case when 1=1 then rand() else NULL end from sys.objects|||UPDATE gvAbsence
SET holiday_brought_forward = CASE WHEN years_of_service < 5 THEN
CASE WHEN 1=1 THEN Floor(Rand() * 6) ELSE NULL END
ELSE
CASE WHEN 1=1 THEN Floor(Rand() * 5) ELSE NULL END
END

Does not give the desired result - (same problem as before)

UPDATE gvAbsence
SET holiday_brought_forward = CASE Floor(Rand()*5)
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END

Same goes for this *sigh*
I can understand the use of CASE statements to make sure every row is evaluated individualy - but that doesn't appear to be the case when running the above...

this might sound silly but in other languages you need to use

Randomize

Would that apply here? *shrugs*
thanks for the input so far :)|||Can you create a derived table and update from that rather than directly with the case statement?|||Do you mean something like this?

IF EXISTS (SELECT NULL FROM sysobjects WHERE type='U' AND name='gvVals') BEGIN
DROP TABLE gvVals
END

CREATE TABLE gvVals
(
bought_forward int
)

INSERT INTO gvVals(bought_forward) VALUES(1)
INSERT INTO gvVals(bought_forward) VALUES(2)
INSERT INTO gvVals(bought_forward) VALUES(3)
INSERT INTO gvVals(bought_forward) VALUES(4)
INSERT INTO gvVals(bought_forward) VALUES(5)
INSERT INTO gvVals(bought_forward) VALUES(6)

UPDATE gvAbsence
SET holiday_brought_forward = CASE Floor(Rand()*6)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END
FROM gvVals

Holiday Brought Forward
--
5.0
5.0
5.0
5.0
5.0
etc|||Text file containing the cursored SQL uploaded for your amusement / berrating ;)
EDIT: and I guess comments, too|||I could not get different values for rand() using the case statement on SS2K
Generating a random value from newid() seems to work
UPDATE gvAbsence
SET holiday_brought_forward =
right(convert(char(2),(convert(int, (convert(varbinary(1),right(newid(),1))))) ),1)/10.*5+1|||pdreyer - wow, that appears to work (and quickly!)
But I'm afraid I have absolutely no idea what it is doing ;)
/10.*5+1 ?? I removed the point and it didn't work - what's that doing :)

Seems like an aweful lot of converts in there and I can't for the life of me understand the char convert!

Hehe, thanks for that - I'd love to implement it, but only if I can understand it first :D

EDIT: I have removed the +1 as I want to include zero values :) I love the fact it even does halves! I hadn't even considered that before :)
RE-EDIT: Not quite returning the values I need... I need 0-5 inclusive and 0.5's would be lovely (but not essential).
/10.*5 returns 0 - 4.5 inc
/10.*5+1 returns 1 - 5.5 inc|||Do you mean something like this?Nope :)

But it sounds like rand() is working differently in 2k anyway. I don't have any 2k boxes to play with and test so I'll drop out at this point.|||Awww but Pooooooooots!
Thanks for all your prior help bud - it is appreciated :)
and if that's not what you meant... what is! :p|||Did we not cover derived tables in your last thread (or thread but one)? Genuinly can't remember for sure...|||I think I'm lost in jargon ;)
Are derived tables (to put it simply) sub-selects?

And did you see what I did before?

so I'll drop out at this point

I don't know why but I'm finding it harder and harder to ask questions here - easy enough to answer (some) but when I ask them I go into "newb" mode and yeah - it sucks :(|||I think I'm lost in jargon ;)
Are derived tables (to put it simply) sub-selects?
No. But you might be able to do it as a sub select.

Derived table example:

UPDATE gvAbsence
SET holiday_brought_forward = derived_table.random_number
FROM dbo.gvVals
INNER JOIN
(SELECT MyPK
, CASE Floor(Rand()*6)
WHEN 0 THEN 0
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
ELSE 5 END AS random_number
FROM dbo.gvVals) AS derived_table
ON derived_table.MyPK = gvVals.MyPk


And did you see what I did before?Dunno - what did you do?|||I have absolutely no idea what it is doing
it uses the ASCII code of a character from newid() as a random number (caveat: limited range but OK for your needs)
can't understand the char convertNot needed, should be removed.
what's the point doingIt ensure decimal arithmetic else the integer devide will result in zero
it even does halvesThat was not the intended result. I assumed the target field was an integer, So to get values 0-5 multiply by 6 and convert to int at the end. e.g.
select convert(int,right(ascii(newid()),1)/10.*6) from sysobjects