Creativity Required-Subquery, temptable??

  • 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.

  • 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-

  • Unfortunately Table1 is involved in a join with a 3rd table to qualify employee group membership so not all employees are returned.

  • 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-

  • 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