Random numbers

16 Sep 2005 3:56 PM

Mary W.

Hello all!
I'm trying to create a function that returns a random progressive /
regressive series of values (e.g. 11000, 11300, 11900, 12000, 12250) taking
a start value, an end value and (an increment value). Can you please provide
help? Until now I've managed creating the function returning the random
numbers but I can't control the trend...

Thanks,
Mary

16 Sep 2005 4:02 PM

Jerry Spivey

Mary,

An IDENTITY property can be associated with a column that can progressively
increment based on a predefined value from a predefined starting point.

HTH

Jerry
Show quote

"Mary W." <m***@discussions.microsoft.com> wrote in message
news:uGdTGdtuFHA.1472@TK2MSFTNGP15.phx.gbl...
> Hello all!
> I'm trying to create a function that returns a random progressive /
> regressive series of values (e.g. 11000, 11300, 11900, 12000, 12250)
> taking a start value, an end value and (an increment value). Can you
> please provide help? Until now I've managed creating the function
> returning the random numbers but I can't control the trend...
>
> Thanks,
> Mary
>

16 Sep 2005 7:16 PM

David Gugick

Mary W. wrote:

> Hello all!
> I'm trying to create a function that returns a random progressive /
> regressive series of values (e.g. 11000, 11300, 11900, 12000, 12250)
> taking a start value, an end value and (an increment value). Can you
> please provide help? Until now I've managed creating the function
> returning the random numbers but I can't control the trend...
>
> Thanks,
> Mary

You could try something like this (only slightly tested). You can't use
the RAND() function in a function, so I used a stored procedure. You
could pass in the RAND() value into a function and do it that way.

Alter Procedure dbo.RandomGen (
@iStart INT,
@iEnd INT,
@iIncrement INT,
@iRand INT OUTPUT )
as
BEGIN
Declare @iNum INT

SET @iNum = CAST((@iEnd - @iStart) / @iIncrement as INT)

SET @iRand = @iStart + (@iIncrement * CAST(ROUND(@iNum * RAND(), 0) as
INT))

END

Declare @iRan INT
Exec dbo.RandomGen 1, 10, 1, @iRan OUTPUT
Print @iRan

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

19 Sep 2005 7:30 AM

Mary W.

Hello,
thanks for your replies!

The point is that I need an autoincrement function. I managed to come so far: I created a view that uses rand:

create view vwrandom as select rand() as random

and a function that uses the view:

create function dbo.fnrandom(@Min decimal(19,2), @Max decimal(19,2))
returns decimal (19,2)
as
begin
return (@Min) + (select random from vwrandom) * (@Max-@Min)
end

and when I run

select (dbo.fnrandom(10 ,40)) as [Random], * from pubs..authors

it runs ok, and as you can see the numbers generated are pretty ok :-), between 10 and 40.

What I need is, this function (fnrandom) to take one more param (@increment - let's say value 10) and to autoincrement itself, so the first iteration should be (10,40), the second should be (20, 40), the third (30, 40) and should stop at (40, 40); all should happen in the same result set...

Thanks,
Mary

Show quote

"David Gugick" <david.gugick-nospam@quest.com> wrote in message news:uPglLMvuFHA.3000@TK2MSFTNGP12.phx.gbl...
> Mary W. wrote:
>> Hello all!
>> I'm trying to create a function that returns a random progressive /
>> regressive series of values (e.g. 11000, 11300, 11900, 12000, 12250)
>> taking a start value, an end value and (an increment value). Can you
>> please provide help? Until now I've managed creating the function
>> returning the random numbers but I can't control the trend...
>>
>> Thanks,
>> Mary
>
> You could try something like this (only slightly tested). You can't use
> the RAND() function in a function, so I used a stored procedure. You
> could pass in the RAND() value into a function and do it that way.
>
> Alter Procedure dbo.RandomGen (
> @iStart INT,
> @iEnd INT,
> @iIncrement INT,
> @iRand INT OUTPUT )
> as
> BEGIN
> Declare @iNum INT
>
> SET @iNum = CAST((@iEnd - @iStart) / @iIncrement as INT)
>
> SET @iRand = @iStart + (@iIncrement * CAST(ROUND(@iNum * RAND(), 0) as
> INT))
>
> END
>
> Declare @iRan INT
> Exec dbo.RandomGen 1, 10, 1, @iRan OUTPUT
> Print @iRan
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>