random data within specified range

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)

random data within specified range

Author

Topic

gamcintyre
Starting Member

1 Post

Posted - 2004-08-26 : 14:06:42

Okay, I'm a newbie, so please bear with me. I have written a script that populates a table with some random data, but I would like the random numbers used to fall within a specified range, and if they don't, I want it to try again until it does. Make sense?

Long story short, I'm trying to populate Start/End times for employees by subtracting/adding a random number of minutes to the current system time. What I have works fine, but I'm trying to get the random number used to fall between 30 and 540, and I'm now completely lost.

Here's what I have currently, and the '@minutes' variable is the one in question:

DECLARE @id varchar(9),
@minutes float,
@minutes2 float,
@date int,
@randnum1 int,
@howfarback int

DECLARE Employee_Cursor CURSOR FOR
SELECT CardID FROM EmployeeT WHERE Active = 1
OPEN Employee_Cursor

FETCH NEXT FROM Employee_Cursor INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN

SET @howfarback = 6 --this is the number of days backwards for which you want punches added, indexed from zero(0)
SET @date = 0 --this is so we start with today's date;

WHILE @date <= @howfarback
BEGIN
SET @randnum1 = (SELECT RAND()*1000)
SET @minutes = (round(@randnum1, 0))

INSERT INTO ClockDataT (PunchType, CardID, StartTime) VALUES ('I', @id, DATEADD(mi, -@minutes,(DATEADD(dd, -@date, GETDATE()))))
INSERT INTO ClockDataT (PunchType, CardID, EndTime) VALUES ('O', @id, DATEADD(mi, @minutes, (DATEADD(dd, -@date, GETDATE()))))
SET @date = @date + 1
END

SET @date = 0

FETCH NEXT FROM Employee_Cursor INTO @id
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-26 : 14:22:48

this gives you a random number between 30 an 540:


select top 1 id
from
(select a.id + b.id + c.id as id
from	(select 0 id union all 
	 select 1 union all 
	 select 2 union all 
 	 select 3 union all 
	 select 4 union all 
	 select 5 union all 
	 select 6 union all 
	 select 7 union all 
	 select 8 union all 
	 select 9) a, 
         (select 0 id union all 
         select 10 union all 
         select 20 union all 
         select 30 union all 
         select 40 union all 
         select 50 union all 
         select 60 union all 
         select 70 union all 
         select 80 union all 
         select 90) b, 
         (select 0 id union all 
         select 100 union all 
         select 200 union all 
         select 300 union all 
         select 400 union all 
         select 500) c
        ) d
where id between 30 and 540
order by newid()

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-27 : 08:25:51

ok i just can stand looking at that cursor :))
this should do the same:

select id
into #temp
from
(select a.id + b.id + c.id as id
from (select 0 id union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9) a,
(select 0 id union all
select 10 union all
select 20 union all
select 30 union all
select 40 union all
select 50 union all
select 60 union all
select 70 union all
select 80 union all
select 90) b,
(select 0 id union all
select 100 union all
select 200 union all
select 300 union all
select 400 union all
select 500) c
) d
where id between 30 and 540

WHILE @date <= @howfarback
BEGIN
SET @minutes = (select top 1 id from #temp order by newid())

INSERT INTO ClockDataT (PunchType, CardID, StartTime)
select 'I', CardID, DATEADD(mi, -@minutes,(DATEADD(dd, -@date, GETDATE())))
FROM EmployeeT WHERE Active = 1

INSERT INTO ClockDataT (PunchType, CardID, EndTime)
select 'O', CardID, DATEADD(mi, @minutes, (DATEADD(dd, -@date, GETDATE())))
FROM EmployeeT WHERE Active = 1

SET @date = @date + 1
END

drop table #temp

altough i think u have a bit lousy db design. why have start and end time in a table and the insert two rows spcifying what event is it?? either u have one time column and two rows or two columns and one row.

Go with the flow & have fun! Else fight the flow :)

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-27 : 14:09:35

How about removing the loop all together?


CREATE VIEW v_rand_30_to_540 AS 
	SELECT Cast( (511 * rand() + 30) as bigint) vrand


CREATE Function f_30_to_540() RETURNS Bigint
AS 
BEGIN
	DECLARE @v_rand BIGINT
	SELECT @v_rand = vrand from v_rand_30_to_540
	RETURN @v_rand
END

INSERT INTO ClockDataT (PunchType, CardID, StartTime, EndTime) 
	
	SELECT 
	'I', CardID, DATEADD(mi, - dbo.f_30_to_540() , (DATEADD(dd, -ID, GETDATE()))) StartTime, NULL EndTime
	FROM EmployeeT, 
	( SELECT 0 ID UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) SevenDays
	WHERE Active = 1

	UNION 
	
	SELECT 
	'O', CardID, NULL StartTime, DATEADD(mi, dbo.f_30_to_540() , (DATEADD(dd, -ID, GETDATE()))) EndTime
	FROM EmployeeT, 
	( SELECT 0 ID UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) SevenDays
	WHERE Active = 1

(Edit:Forgot WHERE Active = 1)
--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-27 : 15:06:07

I just realized my suggestion will not work. It does not assign the same random minute offset to the Out type as it does to the In type for a CardID/Day. That means In could be after Out. Sorry :( It could be fixed but I don't have time right now. Even if you use the loop, you can still use SELECT Cast( (511 * rand() + 30) as bigint) to get a random number between 30 and 540.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-28 : 13:25:06

Spirit,

You might find it interesting to look at the nuber table generation at the beginning of this proposed solution.

I tried making it set based, but finally resolved to a cursor in the end.

set nocount on

-- help table variable with 0,1,2,3,4,5,6,7,8,9
declare @nr table( nr int not null primary key )
while ( select count(*) from @nr ) < 10
	insert @nr select count(*) from @nr

-- create number table with numbers from 0 - 999
select a.nr + 10 * b.nr + 100 * c.nr as nr into #numbers
from @nr a cross join @nr b cross join @nr c order by 1


-- create "demo" EmployeeT table with employees 1,2,3,4,5
select top 5 nr + 1 as CardId, Active = 1 into #EmployeeT from #numbers

-- create "demo" ClockDataT table
create table #ClockDataT( PunchType char(1) not null, CardID int not null, StartTime datetime null, EndTime datetime null )


-- declare variables and assign values
declare @howfarback int,
	@cardid int,
	@date datetime,
	@minutes int

set @howfarback = 6 	--this is the number of days backwards for which you want punches added, indexed from zero(0)

-- generate random clock data
declare csr_clockdata cursor for
select
	e.CardId,
	dateadd( day, -n.nr, getdate() ) as dt
from
	#EmployeeT e
	join #numbers n on n.nr between 1 and @howfarback
where
	e.Active = 1

open csr_clockdata

fetch next from csr_clockdata into @cardid, @date
while @@fetch_status = 0
begin
	set @minutes = ( select top 1 nr from #numbers where nr between 30 and 540 order by newid() )
	insert #ClockDataT( PunchType, CardID, StartTime, EndTime )
	select 'I', @cardid, dateadd( minute, -@minutes, @date ), null
	union all
	select 'O', @cardid, null, dateadd( minute, @minutes, @date )
	fetch next from csr_clockdata into @cardid, @date
end

close csr_clockdata
deallocate csr_clockdata

print '#EmloyeeT'
select * from #EmployeeT
print '#ClockDataT'
select * from #ClockDataT

-- cleanup
drop table #numbers
drop table #EmployeeT
drop table #ClockDataT

/rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-28 : 14:35:50

hey rockmoose, yeah i thought i'd write it the way you did. acctually i do choose random numbers in your way, but i thought that using three tables would be easier to understand. after all, i have no idea how much sql the poster knows. and when i was writing it i didn't have the time for a larger explanation.

ok, i don't get it, why did you use a cursor??

Go with the flow & have fun! Else fight the flow :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-28 : 14:49:00

Hi spirit,

quote:
ok, i don't get it, why did you use a cursor??

Well I wanted a random minute value for each combination of:
CardId + Date

So I create the cursor like so:

select
	e.CardId,
	dateadd( day, -n.nr, getdate() ) as dt
from
	#EmployeeT e
	join #numbers n on n.nr between 1 and @howfarback
where
	e.Active = 1

This will get all these combinations,
I then cursor through this set assigning random minute values to each.

I could have done a while loop using some logic, but I thought this would be more time consuming.

In your solution the same random minute value is assigned to all the Date values for each Employee.
I didn't think this was random enough ;-)

Order is just a lesser state of chaos,
/rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-28 : 14:54:38

randomize the randomness :)))) -> chaos

Go with the flow & have fun! Else fight the flow :)

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-29 : 17:50:44

I took another look at my suggestion. This should do it without a loop.


--Some sample data
SELECT 1 CardID, 1 Active INTO EmployeeT UNION SELECT 2,1 UNION SELECT 3,1 UNION SELECT 4,1
--Output Table
CREATE TABLE ClockDataT (PunchType CHAR, CardID int, StartTime datetime, EndTime datetime) 

--View to return random number from 30 to 540
CREATE VIEW v_rand_30_to_540 AS  SELECT Cast( (511 * rand() + 30) as bigint) vrand

--Function to return random number from 30 to 540
CREATE Function f_30_to_540() RETURNS Bigint
AS 
BEGIN
	DECLARE @v_rand BIGINT
	SELECT @v_rand = vrand from v_rand_30_to_540
	RETURN @v_rand
END
GO

--Build temporary list of CardID's and random minute offsets for 7 days
SELECT CardID, dbo.f_30_to_540() OffSet, ID
	INTO #ClockOffsets
	FROM EmployeeT,	
	( SELECT 0 ID UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) SevenDays
	WHERE Active = 1
	ORDER BY CardId, ID DESC

--Insert In and Out times into ClockDataT

INSERT INTO ClockDataT (PunchType, CardID, StartTime, EndTime) 
	SELECT PunchType, CardID, 
		CASE PunchType When 'I' Then 
			DATEADD(mi, - OffSet, (DATEADD(dd, -ID, GETDATE()))) 
		ELSE 
			NULL
		END StartTime, 
		CASE PunchType When 'O' Then 
			DATEADD(mi, OffSet, (DATEADD(dd, -ID, GETDATE()))) 
		ELSE 
			NULL
		END EndTime
	FROM #ClockOffsets,
	(SELECT 'O' PunchType UNION SELECT 'I') x
	ORDER BY CardID, ID DESC, PunchType

SELECT * FROM ClockDataT

PunchType CardID      StartTime                                              EndTime                
--------- ----------- ------------------------------------------------------ -----------------------
I         1           2004-08-23 14:30:00.393                                NULL
O         1           NULL                                                   2004-08-23 19:04:00.393
I         1           2004-08-24 15:18:00.393                                NULL
O         1           NULL                                                   2004-08-24 18:16:00.393
...

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources