?
Solved

I have to randomley select three names out of a table in a database

Posted on 2003-03-28
2
Medium Priority
?
181 Views
Last Modified: 2006-11-17
The employee table contains the ID and Name.  

I am required to set up a query that randomly selects three employees.  At the same time transfers those names to a seperate table with the date they were selected.

When the query selects the employees, it also has to check that they have not been selected within the last six months.

Can anybody give any clues.

Tess
0
Comment
Question by:princewarlow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 8224999
select top 3 e.[id] into #newemp
from employee e
left join selectedemployee se on e.[id]=se.[id]
where se.[id] is null
order by NEWID()
insert selectedemployee([id])
select [id] from #newemp
select e.[id],e.[name]
from #newemp t
join employee e on t.[id]=e.[id]
drop table #newemp
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 225 total points
ID: 8226851
good luck...
 hth




/*

  Randomly Select 3 Employees.....

*/

set nocount on

/*
drop table #ta
drop table #tb
drop table #tc
*/

declare @TotalEmployees integer
declare @rand integer
declare @randseed integer
declare @randadd char(3)
declare @timedelay datetime
declare @Last integer
declare @Next integer

select @last=0,@next=0
      ,@TimeDelay=DateAdd(Month,7,GetDate())  -- Do not reselect with in 6 months so use 7....

/*
  Select all valid  Employees
*/

--Select distinct Client_reference as EmpId into #ta from e2replica..iqh001tb

select distinct EmpID ,into #ta
 from Employees as E
 where end_date='31 DEC 9999'  -- check for valid employment status....
  and Not Exists (Select S.Empid from SelectedEmployees as S
                   Where S.Empid = E.EmpId
                     and S.SelectedDate > @TimeDelay)
order by EmpID

/*
  Assign each valid employee a "RowNumber"
*/
select EmpId,Identity(int,1,1) as Num
  into #tb
  from #ta a
   
select @TotalEmployees=count(*) from #ta

create Table #tc (Empid char(9))

set @randadd="000"

/*
  purpose of the code is to get a valid random number.....
  closely related seeds can lead to "sequentially" incremented numbers....
  the Waitfor delay ensures the base seed will be different on each iteration
    (otherwise the base timeinterval is likely to be generated...)      

*/


While 3 > (Select count(*) from #tc)
begin
       SELECT @randseed =   (DATEPART(mm, GETDATE()) * 100000 )
                               + (DATEPART(ss, GETDATE()) * 1000 )
                               +  DATEPART(ms, GETDATE())  
       Select @randseed = @randseed + 1 + Convert(integer,@randadd)
       SELECT @rand = 1 + convert(integer,@TotalEmployees * RAND(@randseed))
       select @Next = @last + @rand
       select @next=case when @next > @TotalEmployees then @next - @TotalEmployees else @next end
       Insert into #tc
              select EmpID from #tb
               where num = @next  
                 and not exists (select #tc.EmpID from #tc where #tc.Empid = #tb.Empid)
       Select @Last=@Next
             ,@randAdd= left(convert(varchar(9),@rand) + space(3),3)
       select @timedelay = convert(datetime,'000:00:00.' + @randadd)
       Waitfor Delay @timedelay
  end

/*
create table selectedEmployees (Empid char(9) not null,SelectedDate datetime not null , primary key (Empid,SelectedDate))
*/
Insert Into SelectedEmployees (EmpId, SelectedDate)
select Empid, GetDate() from #tc

drop table #ta
drop table #tb
drop table #tc
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question