Have Geek Brain

SQL Query Help: Pick a Random Employee of the Month

Can anyone show me how to get an entry from this statement

  • Pick a random employee who hasn't been an employee of the month in the last x months.

Employee table
ID
EmployeeName

EmployeeOfTheMonth
ID Table
EmployeeID
MonthStartedDate
MonthEndedDate

Many thanks

+1


a source to share


2 answers 


select top 1 id from employee as emp 
where not exist(
    select top 1 * 
      from employeeofthemonth as em 
      where em.id = emp.id and dateadd(m, -6, getdate()) < monthendeddt )
order by newid()


... or something close to that. i didn't run sql but on mssql server it should be roughly correct.

+2


a source


Modification of the last answer, I prefer to use IN statment


DECLARE @xMonth INT
SET @xMonth = 3


SELECT TOP 1 ID FROM Employee WHERE ID NOT IN (SELECT EmployeeID FROM EmployeeOfTheMonth WHERE DATEADD(m, -@xMonth, GETDATE()) < MonthEndedDate) ORDER BY NEWID()



0


a source






More articles:


All Articles