I realize that, as this uses a cursor, it's the ultimate evil; however:

SELECT Timecard.EmpID,

SUM(Timecard.WrkHrs) AS WrkHrs,

WrkEmp.FName AS EmpFName,

WrkEmp.LName AS EmpLName,

MgrEmp.EmpID AS MgrID,

MgrEmp.FName AS MgrFName,

MgrEmp.LName AS MgrLName

INTO #EmpHoursByProject

FROM Timecard

INNER JOIN Project ON Timecard.ProjNum = Project.ProjNum

INNER JOIN Employees MgrEmp ON Project.mbillaty = MgrEmp.EmpID

INNER JOIN Employees WrkEmp ON Timecard.ttk = WrkEmp.EmpID

WHERE (Timecard.HrsDate >= CONVERT(DATETIME, '10/1/2001', 102))

AND (Timecard.HrsDate < CONVERT(DATETIME, '7/1/2002', 102))

GROUP BY Timecard.EmpID,

WrkEmp.FName,

WrkEmp.LName,

MgrEmp.EmpID,

MgrEmp.FName,

MgrEmp.LName

HAVING (Timecard.EmpID <> MgrEmp.EmpID)

--lazy man's table creation script

SELECT *

INTO #TopFiveEmpProj

FROM #EmpHoursByProject

WHERE EmpID <> EmpID

DECLARE @curEmpId int -- assumes EmpID is an int

DECLARE empCursor LOCAL CURSOR FOR

SELECT DISTINCT EmpID FROM #EmpHoursByProject

OPEN empCursor

FETCH empCursor INTO @curEmpID

WHILE (@@FETCH_STATUS = 0)

BEGIN

INSERT INTO #TopFiveEmpProj

SELECT TOP 5 *

FROM #EmpHoursByProject

WHERE EmpID = @curEmpId

ORDER BY WrkHrs DESC

FETCH empCursor INTO @curEmpID

END

CLOSE empCursor

DEALLOCATE empCursor

DROP TABLE #EmpHoursByProject

SELECT *

FROM #TopFiveEmpProj

ORDER BY EmpID,WrkHrs DESC

I believe this will get you what you need. Now, everyone else can come in and tell you hwo to get it ten times faster, with 1/10 the memory. 🙂

RD Francis