June 5, 2003 at 10:03 am
I have the need to produce a report of hours worked for individual employees who have been with the company less then 3 years. The twist is that 'we' only need to see the top 5 projects each employee worked on for a specific time period.
If I do the following I get back all of the projects each employee worked on (where he was not also the Project Manager) for a specic time period sorted by EmployeeID and Total Hours Worked on each project.
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
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)
ORDER BY Timecard.EmpID, SUM(Timecard.tworkhrs) DESC
But how (if I can at all) do I get it return only the five projects each employee worked the most hours on?
June 5, 2003 at 4:17 pm
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
R David Francis
June 6, 2003 at 1:54 am
I'm not going to write a query here. Just some ideas. If you need more help, I'm glad to help you out.
Your query will look something like
SELECT <all your main stuff here>
FROM ... OuterFrom1
WHERE ProjNum IN
(SELECT TOP 5 ProjNum
FROM ...
WHERE OuterFrom1.EmpId = SubQuery1.EmpID)
Maybe a bit of explanation :
In the subquery in the where clause, you select the TOP 5 projects that an employee has worked on.
So you will have to bind the employeeid of the subquery to that of the mainquery.
Using the IN clause, you only select projects that are in the TOP 5 in your main query. With those results, you can do anything you want.
If you want some more elaborate query, please post the structure of your tables. That makes it easier to get things right...
June 6, 2003 at 2:38 am
As NPeeters pointed out a correlated subquery is the best way forward. Another option (less good) is to use derived tables.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply