Luis Cazares (10/6/2014)

---

If you're creating a random column, you could use it twice to ensure that you're not hitting the same employee.

CREATE TABLE tblEmployee(

[id] int IDENTITY

,[employeeName] varchar(100)

,[employeeCode] char(5)

,[employeeEmail] varchar(100)

,[retired] bit

)

INSERTtblEmployee VALUES

('Luis', 'A', 'Luis@SSC.com',0),

('Peter', 'A', 'Peter@SSC.com',0),

('John', 'A', 'John@SSC.com',0),

('Michael', 'C', 'Michael@SSC.com',0),

('Mary', 'C', 'Mary@SSC.com',0),

('Wayne', 'TK', 'Wayne@SSC.com',0);

SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS Eggs

,[id]

,[employeeName]

,[employeeCode]

,[employeeEmail]

INTO #Random

FROM [dbo].[tblEmployee]

WHERE retired = 0 AND employeeEmail <> '' AND employeeCode <> 'TK'

SELECT r.Eggs, r.employeeName, r.employeeEmail, r2.Eggs, r2.employeeName, r2.employeeEmail

FROM #Random r

JOIN #Random r2 ON r.Eggs + 1 = r2.Eggs --join with the next row

OR ( r2.Eggs =1 AND r.Eggs = (SELECT COUNT(*) n FROM #Random)) --join the first and the last rows

DROP TABLE #Random

GO

DROP TABLE tblEmployee

EDIT: Changed the CTE into a temp table because the Eggs column was being calculated twice.