Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Need alernative to cursor

    Hey guys and gals - here's my current code
    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

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

    -George
    Last edited by gvee; 05-17-07 at 06:35. Reason: missed out a line ;)
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Which 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.
    Code:
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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?
    Code:
    --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.
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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

    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
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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.

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My understanding was that rand won't work in a set based query.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If I was to keep my cursor, I could use something like a counter instead of a random number for consistancy.
    Something like
    Code:
    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...
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  11. #11
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Unfortunately I'm using SQL Server 2000; TOP won't work.
    George
    Home | Blog

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!
    Code:
    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
    Last edited by gvee; 05-18-07 at 05:54. Reason: Colouring in :)
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Unfortunately I'm using SQL Server 2000; TOP won't work.
    Howdya figure that?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I didn't think TOP was implemented in 2000?
    Any time I try run a TOP statement in QA I get
    Code:
    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!
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •