SSRS SQL help with multiple tables showing all employees
Posted on 2012-03-22
I have an employee summary report that works well but doesn't show employees when they haven't submitted hours for the defined time period.
My query is
SELECT Employee.Empfname + ' ' + WIP.WEmpLName AS Employee, WIP.WCltName AS Project, WIP.WSCDesc AS Service, SUM(WIP.Whours) AS Hours,
WIP.Wfee AS Amount, WIP.Wdate AS Date, Department.DeptName AS Team, WIP.WCodeCat AS ServiceCategory
FROM WIP INNER JOIN
Employee ON WIP.WempID = Employee.ID INNER JOIN
Department ON Employee.Empdept = Department.DeptID
WHERE (WIP.WempID > 1) AND (WIP.Wdate BETWEEN @StartDate AND @StartDate + 6) AND (WIP.WCodeCat IN ('Admin', 'Bill'))
GROUP BY Employee.Empfname, WIP.WEmpLName, WIP.WCltName, WIP.WSCDesc, WIP.Wdate, WIP.Wfee, Department.DeptName, WIP.WCodeCat
ORDER BY Date, Employee, ServiceCategory
I need all the employees in table Employee with a Empstatus of Active to show up on my summary even if they don't have any records of time in WIP table. I'm not sure if my code does this or not but they should show up with time/hours 0. I would think that all I should have to do is change WIP INNER JOIN Employee to WIP RIGHT OUTER JOIN Employee to show all the records from the right table (Employee) even if there are no matching records in WIP. Doing this doesn't change my results (this could be because everyone is working now with year end) but I don't know for sure if that is the case.
I went to the Employee table and queried for all active employees and got 38 active employees. So when I run the report it should show results for 38 employees right? I am grouping all the information on employee and implemented drilldowns for more detailed information on the hours billed by their service to the project.