How to generate random number for the value in th

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to generate random number for the value in th

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-02 : 11:27:38

How to create random number for the value in other colum. Please help and urgent

I have a table with 4 column ( ID, Ori_Quantity, Rand_Quantity, Location)

If Ori_quantity < 5000 then
Rand_quanty as qty = I want Random number within 100
Else If Ori_quantity = 0 or < 10 then
Rand_quanty as qty = I want Random number within 7
End if

From tblname where Location = 'DAS'

I have around 2500 field. So when I run the query I expect the result should be
like below

Ori_Quantity, Rand_Quantity
------------ -------------
4000 90
2986 57
2500 89

I don't want to pass any parameter and I am new. Please help

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-02 : 11:29:46

use rand() function. it returns values from 0 to 1 so you'll have to multiply it by desired koeficient

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 11:52:14

1 + ABS(CHECKSUM(NEWID()) % 100

gives a random number between 1 and 100 very fast.

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-02 : 13:37:24

quote:
Originally posted by Peso

1 + ABS(CHECKSUM(NEWID()) % 100

gives a random number between 1 and 100 very fast.

E 12°55'05.25"
N 56°04'39.16"

I think you left out a )

(ABS(CHECKSUM(NEWID()))%100)+1

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 15:01:16

That's right.

1 + ABS(CHECKSUM(NEWID())) % 100

E 12°55'05.25"
N 56°04'39.16"

Vaishu
Posting Yak Master

178 Posts

Posted - 2007-08-02 : 19:42:16

Guys
IT works thanks a lot.Great help!!!!!!!

BUT HOW DO I USE THIS INSIDE 'IF' CONDITION.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-03 : 02:49:02

quote:
Originally posted by Vaishu

Guys
IT works thanks a lot.Great help!!!!!!!

BUT HOW DO I USE THIS INSIDE 'IF' CONDITION.

Thanks

If Ori_quantity < 5000 then
select 1 + ABS(CHECKSUM(NEWID())) % 100 as qty
Else If Ori_quantity = 0 or < 10 then
select 1 + ABS(CHECKSUM(NEWID())) % 7 as qty
End if

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-03 : 03:28:12

Use ELSE, or you will fail.

SELECT CASE WHEN Ori_quantity < 10 then 1 + ABS(CHECKSUM(NEWID())) % 7
WHEN Ori_quantity < 5000 then 1 + ABS(CHECKSUM(NEWID())) % 100
else -1
end aS rand_quantity
From tblname where Location = 'DAS'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources