SSRS SQL help with multiple tables showing all employees

Posted on 2012-03-22
Medium Priority
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 35

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 35

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.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 35

Accepted Solution

James0628 earned 2000 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 35

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

616 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