Solved

SSRS SQL help with multiple tables showing all employees

Posted on 2012-03-22
7
378 Views
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
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.
0
Comment
Question by:HSI_guelph
  • 4
  • 3
7 Comments
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
 

Author Comment

by:HSI_guelph
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:HSI_guelph
Comment Utility
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
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:HSI_guelph
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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

11 Experts available now in Live!

Get 1:1 Help Now