SSRS SQL help with multiple tables showing all employees

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
                      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.
Who is Participating?
If you're specifically looking for the people that haven't entered their time, maybe you should just look for those people.  Maybe change the WHERE and replace

(WIP.Wdate BETWEEN @StartDate AND @StartDate + 6)

  with something like

WHERE Wdate BETWEEN @StartDate AND @StartDate + 6))

 You'd probably want to make some other changes, like maybe just remove some columns like WIP.Whours, WIP.Wfee and WIP.Wdate, since they probably wouldn't mean anything here.

 Also, FWIW, going back to your original post, if every employee has at least one entry in WIP, a Right Outer Join won't make any difference.  That would include anyone that did not have any entries in WIP, which won't change anything if everyone has an entry.

Changing to a RIGHT OUTER JOIN should include all rows from Employee, but then you check various WIP columns in your WHERE, which would filter out any employees without a WIP row.  So, to start with, I think you need to change your WHERE.  For example, change

(WIP.WempID > 1)

   to something like


 Actually, in that case, if you're looking for specific employee ID's, you might just want to replace (WIP.WempID > 1) with (Employee.ID > 1).  But you presumably still need to handle nulls in WIP.Wdate and WIP.WCodeCat.

 You may also need some other changes, like changing SUM (WIP.Whours) to something like SUM (ISNULL (WIP.Whours, 0)), to avoid warnings about nulls in the aggregate.  There are also questions about how your GROUP BY and ORDER BY should work, since they're based, at least in part, on values from WIP.  What are they supposed to use if there is no WIP row for an employee?

HSI_guelphAuthor Commented:
It should list all employees alphabetically by teams (want to let the user choose the team to display so not sure if it needs to be sorted by teams).  I'm wondering if it would be better to grab the data and do the summing, sorting, grouping within the report.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

FWIW, if the user is always going to select a single team, then I don't think there's any need to sort by the team.

 But where is the team defined?  If that's in WIP, how do you know the team for an employee when they don't have any rows in WIP?

 This next question may answer that last one.

 I was assuming that some employees had no entries at all in WIP.  Is that correct, or does every employee have at least one entry, but maybe not for the desired dates?

 If so, then maybe you just need to change the query so that it will include employees that have entries in WIP, but not for the desired dates.  For example, remove the date check from the WHERE, and change

SUM(WIP.Whours) AS Hours,


  WHEN WIP.Wdate BETWEEN @StartDate AND @StartDate + 6 THEN
 END) AS Hours,

 That way the employees that have WIP entries, but not for those dates, will be included, but Hours will be 0.

HSI_guelphAuthor Commented:
The team is defined in the Employee table.  I need the employee id in WIP to link back to the Employee table to get the # for the team which I then need to go to the Team (Department) to get the name (think I might make seperate datasets to pass id parameters to get descriptions to display).

Employees will all have entries in WIP if you look at all the data available (back to 2006) but they may not have entries in for the date range provided.  This report will be sent every Tuesday to show the hours for the past week which should have been entered in by Monday so we can easily see at a glance who hasn't entered their time yet.
HSI_guelphAuthor Commented:
Thank you for your help!  I think that what I want to do isn't possible in the context I'm trying to do it in but I like the idea of seperating the ppl with no hours from those that have hours.
FWIW, I think the query could include the people with no WIP entries for the specified dates, but you're using a number of columns from WIP, so there's a question about how you handle those columns.  Do you use values from one of their existing WIP entries (from a date that's outside your range), or substitute null or some other "default" value?  That might depend on what's in WIP.  For example, if a person could have WIP entries with two different WIP.WCltName values, do you use one of those?  If so, how do you decide which one?

 Actually, if you can decide how to handle those WIP columns for the people that did not have entries for your date range, it just occurred to me that one simple way to include the people with and without entries would be two SELECT's, with a UNION.  Use your original query to get the people with entries in your date range, and then a second query with the NOT IN (SELECT ...) test in my last post, to get the people without entries in that range.

 Using a separate query/report to see only the people that have not entered time still seems like the easiest way to identify those people, but if you need a combined report, it might be possible.  It depends a lot on your data.  Does including people that don't have WIP entries for those dates with the people that do even make sense?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.