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
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.
HSI_guelphAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
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

(WIP.WempID IS NULL OR WIP.WempID > 1)


 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?

 James
0
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.
0
James0628Commented:
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,

   to

SUM(
 CASE
  WHEN WIP.Wdate BETWEEN @StartDate AND @StartDate + 6 THEN
    WIP.Whours
  ELSE
    0
 END) AS Hours,


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

 James
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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.
0
James0628Commented:
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

(WIP.WempID NOT IN (SELECT DISTINCT WempID FROM WIP
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.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
James0628Commented:
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?

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.