SQL JOIN - Show all Employees and Specific Records from joining table

The scope on my project has changed a bit.  The query below worked fine until management decided that they wanted to show employee's that had zero time entered as well.

I now need to show a list of employee's hours (even if there is no record of hours entered), along with their Office, Skill Level and Department for a specifc date range.  Before I didn't have to show the employee if no time was entered.

 
Needed Output:

 EmployeeName        EBDate               EBChgHours EBNonHours OffName DeptName SKDesc
 ------------        ------               ---------- ---------- ------- -------- ------
 Doe, John E (1111)  2010-02-01 00:00:00  10.75      2.25       City    DeptA    Manager
 Abe, Jane A (2222)                       0          0          CityB   DeptB    Associate

Open in new window


Original Query:
 
SELECT     Employee.Emplname + ', ' + Employee.Empfname + ' (' + Employee.Empnum + ')' AS EmployeeName, EmpBudHRS.EBDate, EmpBudHRS.EBChgHours, 
                      EmpBudHRS.EBNonHours, Office.OffName, Department.DeptName, SkillLevel.SkDesc
FROM         Employee LEFT OUTER JOIN
                      EmpBudHRS ON Employee.ID = EmpBudHRS.EBEmpID RIGHT OUTER JOIN
                      Office ON Employee.Empoff = Office.OffID INNER JOIN
                      Department ON Employee.Empdept = Department.DeptID INNER JOIN
                      SkillLevel ON Employee.Emplevel = SkillLevel.SkID
WHERE     (EmpBudHRS.EBDate BETWEEN @begdate AND @enddate)
ORDER BY Employee.Emplname

Open in new window


Thanks
ITMcmcpaAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Try this one
SELECT  Employee.Emplname + ', ' + Employee.Empfname + ' (' + Employee.Empnum
        + ')' AS EmployeeName ,
        EmpBudHRS.EBDate ,
        ISNULL(EmpBudHRS.EBChgHours, 0) ,
        ISNULL(EmpBudHRS.EBNonHours, 0) ,
        Office.OffName ,
        Department.DeptName ,
        SkillLevel.SkDesc
FROM    Employee
        INNER JOIN Office ON Employee.Empoff = Office.OffID
        INNER JOIN Department ON Employee.Empdept = Department.DeptID
        INNER JOIN SkillLevel ON Employee.Emplevel = SkillLevel.SkID
        LEFT OUTER JOIN EmpBudHRS ON Employee.ID = EmpBudHRS.EBEmpID AND EmpBudHRS.EBDate BETWEEN @begdate AND @enddate
ORDER BY Employee.Emplname

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
How this query works ?
SELECT  Employee.Emplname + ', ' + Employee.Empfname + ' (' + Employee.Empnum
        + ')' AS EmployeeName ,
        EmpBudHRS.EBDate ,
        ISNULL(EmpBudHRS.EBChgHours, 0) ,
        ISNULL(EmpBudHRS.EBNonHours, 0) ,
        Office.OffName ,
        Department.DeptName ,
        SkillLevel.SkDesc
FROM    Employee
        INNER JOIN Office ON Employee.Empoff = Office.OffID
        INNER JOIN Department ON Employee.Empdept = Department.DeptID
        INNER JOIN SkillLevel ON Employee.Emplevel = SkillLevel.SkID
        LEFT OUTER JOIN EmpBudHRS ON Employee.ID = EmpBudHRS.EBEmpID
WHERE   ( EmpBudHRS.EBDate BETWEEN @begdate AND @enddate )
ORDER BY Employee.Emplname

Open in new window

0
 
ITMcmcpaAuthor Commented:
Unfortunately this query still only pulls Employees that have a record (time entered) in the EmpBudHRS table.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rajkumar GsSoftware EngineerCommented:
Just to test - Try this query. Getting records those have no record in Hours table ?
SELECT * 
FROM Employee E
	INNER JOIN EmpBudHRS H ON E.ID = H.EBEmpID
WHERE H.EBDate BETWEEN @begdate AND @enddate

Open in new window

0
 
OnALearningCurveCommented:
Hi ITMcmcpa,

If I have understood this correctly you are currently showing staff who had hours logged between two dates (@begdate AND @enddate)

and now you need to show all staff who fall into this bracket along with any staff who have no entry at all in the EmpBudHRS table.

If this is right then your current WHERE clause will exclude anyone with not entry in the EmpBudHRS table.

so try changing

WHERE     (EmpBudHRS.EBDate BETWEEN @begdate AND @enddate)

to

WHERE     (EmpBudHRS.EBDate BETWEEN @begdate AND @enddate) OR EmpBudHRS.EBDate IS NULL

and I believe you will get your missing details.

Hope this helps,

Mark
0
 
ITMcmcpaAuthor Commented:
@RajkumarGS:
The last one seems to have done the trick.  Let me do a little more testing and then I will accept.
Thanks!
0
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.