RAND() with range - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

RAND() with range

Author

Topic

kingroon
Starting Member

29 Posts

Posted - 2007-04-25 : 20:59:29

Hi..

I need to populate a two columned table with about 600 records, first column is a random number between [e.g.] 1.5 and 2, the second a datestamp every 20 minutes over an historical 5 day period [e.g. last workday week].

Has anyone had experience with this? Any suggestions?

Cheers..
Matt

DogFightClothing. No dogs. No fighting.
http://www.dogfightclothing.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 21:37:58

for random number you use newid()

datestamp, use select dateadd(minute, NUMBER * 20, @start_date) from F_TABLE_NUMBER_RANGE(1, @no_of_period)

using F_TABLE_NUMBER_RANGE from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-26 : 00:32:37

This code should do what you want. I also added one more column to demo how to generate a random datetime within each 20 minute range.


declare @t table (
Number int not null primary key clustered,
Rand_Int int not null,
Random_Time datetime not null
)

insert into  @t
select
	Number,
	-- Function F_RANDOM_INTEGER available in Script Library forum
	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499
	-- Get integer in range of 1 to 1,500,000,000 to 2,000,000,000
	Random_Int = 
		[dbo].[F_RANDOM_INTEGER](1500000000,2000000000,newid()),
	-- Function F_RANDOM_DATETIME available in Script Library forum
	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499
	-- Get random time in 20 minute range
	Random_Time = 
		[dbo].[F_RANDOM_DATETIME]('00:00:00.000','00:20:00.000',newid())
from
	-- Function F_TABLE_NUMBER_RANGE available in Script Library forum
	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
	F_TABLE_NUMBER_RANGE(0,599)


select
	--Number,
	Rand_Num	= convert(numeric(15,9),Rand_Int*0.000000001),
	Time_Period	= dateadd(minute,NUMBER*20,'20070429'),
	-- Random time in 20 minute range
	Rand_Time	= dateadd(minute,NUMBER*20,'20070429')+Random_Time
from
	@t a
order by
	NUMBER



Results:

(600 row(s) affected)

Rand_Num          Time_Period                                            Rand_Time
----------------- ------------------------------------------------------ ------------------------
1.840428869       2007-04-29 00:00:00.000                                2007-04-29 00:09:33.013
1.670566966       2007-04-29 00:20:00.000                                2007-04-29 00:34:32.977
1.895743348       2007-04-29 00:40:00.000                                2007-04-29 00:49:37.477
1.881807968       2007-04-29 01:00:00.000                                2007-04-29 01:00:19.497
1.528415225       2007-04-29 01:20:00.000                                2007-04-29 01:24:04.143
1.614305759       2007-04-29 01:40:00.000                                2007-04-29 01:48:35.000
...
...
1.573224994       2007-05-07 06:20:00.000                                2007-05-07 06:21:14.867
1.688978319       2007-05-07 06:40:00.000                                2007-05-07 06:52:06.613
1.967219298       2007-05-07 07:00:00.000                                2007-05-07 07:11:00.167
1.867450782       2007-05-07 07:20:00.000                                2007-05-07 07:24:38.910
1.614694445       2007-05-07 07:40:00.000                                2007-05-07 07:46:18.590

(600 row(s) affected)

CODO ERGO SUM

Edit: Added code I missed with cut/paste

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 00:37:50

Ah ! Was not aware about the F_RANDOM_INTEGER().

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-26 : 01:00:04

I think the F_RANDOM_DATETIME is very useful for generating random times within a time range to generate test data.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 01:03:12

quote:
Originally posted by Michael Valentine Jones

I think the F_RANDOM_DATETIME is very useful for generating random times within a time range to generate test data.

CODO ERGO SUM

Yes indeed. I was using newid() with F_TABLE_NUMBER_RANGE to do it. This sure will save some typing time and cleaner code.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-26 : 01:17:40

You should be aware of possible issues (bugs/undocumented features) with these in SQL 2000.

You should not use the NEWID() function in a query that does a group by or other aggregate function. There is a discussion of this issue in the thread where I posted these functions. It seems to be fixed in SQL 2005.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 01:20:05

quote:
Originally posted by Michael Valentine Jones

You should be aware of possible issues (bugs/undocumented features) with these in SQL 2000.

You should not use the NEWID() function in a query that does a group by or other aggregate function. There is a discussion of this issue in the thread where I posted these functions. It seems to be fixed in SQL 2005.

CODO ERGO SUM

I found out the hard way

kingroon
Starting Member

29 Posts

Posted - 2007-04-30 : 01:36:39

Thanks guys, very interesting read and helped out heaps..

DogFightClothing. No dogs. No fighting.
http://www.dogfightclothing.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources