SSRS SQL help with multiple tables showing all employees

Posted on 2012-03-22
Last Modified: 2012-03-30
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.
Question by:HSI_guelph
  • 4
  • 3
LVL 34

Expert Comment

ID: 37756125
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?


Author Comment

ID: 37766090
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.
LVL 34

Expert Comment

ID: 37769795
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.

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Comment

ID: 37782330
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.
LVL 34

Accepted Solution

James0628 earned 500 total points
ID: 37785742
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.


Author Closing Comment

ID: 37787237
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.
LVL 34

Expert Comment

ID: 37790042
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?


Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now