April 1, 2003 at 8:34 am
Scenario
Table1 - all employee names
Table2 - hours for all employees
I need to create a list (which I will place in a temptable) of all employees who are under 40 hours for a given week. A record for a person with 0 hours will not be present in table 2, the name will only be present in table 1. The tables do not match.
April 1, 2003 at 9:01 am
Ron, - since I do not understand what you meant by "The tables do not match" this may not be what you are looking for...
I assume there is an Employee ID in each table. Can you do something like
SELECT E.EmpID, E.EmpName, SUM( IsNull(H.EmpHours,0) )
FROM Employee E
LEFT JOIN EmpHours H ON H.EmpID = E.EmpID
GROUP BY E.EmpID, E.EmpName
HAVING SUM( ISNULL( H.EmpHours, 0 ) ) < 40
This is just off the top of my head and may need to be adjusted a little.
Guarddata-
April 1, 2003 at 9:30 am
Unfortunately Table1 is involved in a join with a 3rd table to qualify employee group membership so not all employees are returned.
April 1, 2003 at 9:36 am
Seems like that should still be OK.
SELECT G.Group, E.EmpID, E.EmpName, SUM( IsNull(H.EmpHours,0) )
FROM EmpGroup G
INNER JOIN Employee E ON E.GroupID = G.GroupID
LEFT JOIN EmpHours H ON H.EmpID = E.EmpID
WHERE <Some group condition>
GROUP BY G.GroupID, E.EmpID, E.EmpName
HAVING SUM( ISNULL( H.EmpHours, 0 ) ) < 40
Perhaps if this doesn't work, you could give us a simple table structure to look at.
Guarddata-
April 1, 2003 at 4:07 pm
Sounds like to me it will still work. Just inner join the third table along with the statement suggested, but do not include any fields in your result set from the third table, just use it to qualify the membership. Oh, and I would do that join prior to the left outer join (although I believe it's not so important anymore, as SQL optimizer takes care of it to some extent).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply