Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

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

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
princewarlow
Asked:
princewarlow
1 Solution
 
ispalenyCommented:
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
 
LowfatspreadCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now