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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a bunch