Random Values between 0.25-6.5

Question

Post reply

Random Values between 0.25-6.5

ammit.it2006

Ten Centuries

Points: 1280
More actions
August 31, 2016 at 1:07 am

#316840

Hi all,
I am working on Sql Server 2008 and I have a requirement that for a specific column I have to populate a value between 0.25-6.5. All the values should be in this range. I have 65K records in these values should be distributed.
Please let me know if there is any way. I have to update this value.
Thanks in advance.
Ammy

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Have a look at the code bellow. I think that this is what you are looking for.

create table #t (dc decimal (10,9))

go

insert into #t (dc)

select 6.25 * rand() + 0.25

go 65000

select min(dc), max(dc), avg(dc)

from #t

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ammit.it2006 Ten Centuries Points: 1280 More actions · Answer 2

Hi Adi,

Really you solved my problem in a very short time.

Thanks a lot.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

After looking at the output from RAND(), you may want to read this article:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-set-based-random-numbers/[/url]

RAND() values repeat when doing set-based operations, so many of us use the following formula to generate random numbers.

Abs(Checksum(NewId())) % 10

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Here's an article on generating random integers and floats. The best part is that it doesn't have to go row by row.

http://www.sqlservercentral.com/articles/Data+Generation/87901/

DECLARE @Range decimal(5,2) = 6.5-.25, @StartValue decimal(5,2) = .25;

SELECT SomeRandomFloat = RAND(CHECKSUM(NEWID())) * @Range + @StartValue

FROM sys.all_columns a, sys.all_columns b;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ammit.it2006 Ten Centuries Points: 1280 More actions · Answer 5

Nice one. Really, We don't need to run one by one...........

Thanks

ammit.it2006 Ten Centuries Points: 1280 More actions · Answer 6

Hi,

All these are generating random values but It's not working for any select statement.

Like

In the select these are generating same random value in the select row for all the rows.

So I found another solution.

select

Value = (select ABS(CAST(NEWID() AS binary(6)) %7) + 0.25 )

from Tablename

It's working fine and generating different values.

Also, There was a change in the requirement that I needed the values in the multiple of .25

like

.25, 0.5, .75 etc till 6.25

Thanks

GaryV Hall of Fame Points: 3307 More actions · Answer 7

ammit.it2006 (9/1/2016)
Also, There was a change in the requirement that I needed the values in the multiple of .25
like
.25, 0.5, .75 etc till 6.25
Thanks

That means you have 25 discrete values.

So use one of the schemes above to generate an "integer" value from 1 to 25, and then divide by 4.

I say "integer" in quotes because while you want integer values, you probably want to store them in a decimal.

DesNorton SSC-Insane Points: 24399 More actions · Answer 8

ammit.it2006 (9/1/2016)
Nice one. Really, We don't need to run one by one...........
Thanks

This hsould do the trick

Value = ((select ABS(CAST(NEWID() AS binary(6)) %25)) +1) *0.25

How to post data/code on a forum to get the best help.