Link to home
Start Free TrialLog in
Avatar of spudsoftware
spudsoftware

asked on

Displaying Data that May Not Exist

I'm trying to create a stored procedure that Joins 2 tables - Employees and Hours
The problem is that an employee may not be active or may not have hours for a given week, and I need to represent that missing data with "0.0" but I cant get the query to return any rows for an employee if hours for a given week do not exist.


Select Employee.ID, LastName + FirstName as empName
    SUM(CASE WHEN wh.Regular  Is Not Null THEN wh.Regular  Else '0.0' END)
    SUM(CASE WHEN wh.OverTime Is Not Null THEN wh.OverTime Else '0.0' END)
 
From Employees LEFT OUTER JOIN EmployeeHours as wh on Employee.ID = wh.EmpID
 
Where WeekEnding = @WeekEnding
 
Group By Employee.ID, LastName, FirstName
Order By wh.Regular desc, wh.OverTime desc, empName asc
 
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Daniel Reynolds
Daniel Reynolds
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of spudsoftware
spudsoftware

ASKER

Yep, that did it!
Thanks a bunch