SQL left join is not including all records from left table

I have a requirement to get monthly total hours worked on different tasks for each employee. For that purpose I am writing a stored procedure. But left join is not populating all record from employee table. I know somehow the where condition is filtering out the null values but I really don’t know how to overcome this problem.

ALTER PROCEDURE [dbo].[GetEmployeeWorkHours]
@PageSize smallint = 10,
@CurrentPage smallint = 1,
@EmployeeId varchar(1000) = null,
@StartDate datetime = null,
@EndDate datetime = null
AS

 BEGIN

if(@StartDate is null)
select @StartDate = DATEADD(month, DATEDIFF(month, 0, getDate()), 0)

if(@EndDate is null)
select @EndDate = getDate()

select   e.Id,
e.FirstName + ' ' + e.LastName as [Name],
sum([Minutes]/60.0) as [WorkedHours]
from Employees e
left join TaskResources tr on (e.Id = tr.EmployeeId)
inner join ResourceTaskSchedules rts on (tr.Id = rts.TaskResourceId)
inner join TaskTimeSheets tts on (rts.Id = tts.ResourceTaskScheduleId)
where
((@EmployeeId is null) or (e.Id in (select splitdata from dbo.SplitEmployeeIds(@EmployeeId, ',')))) and
cast(tts.CheckIn as date) >= @StartDate
and cast(tts.CheckIn as date) <= @EndDate
group by
e.Id
ORDER BY
 e.Id
OFFSET (@CurrentPage-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY; 

END

Answer

Once you use left join in a from clause, you normally continue with left join. This is critical if the second table is used for any conditions.

Then, any filtering conditions on any of the left join’ed tables need to be in the on clauses, not the where clause. So:

from Employees e left join
     TaskResources tr
     on e.Id = tr.EmployeeId left join
     ResourceTaskSchedules rts
     on tr.Id = rts.TaskResourceId left join
     TaskTimeSheets tts
     on rts.Id = tts.ResourceTaskScheduleId and
        cast(tts.CheckIn as date) >= @StartDate and
        cast(tts.CheckIn as date) <= @EndDate
where ((@EmployeeId is null) or 
       (e.Id in (select splitdata from dbo.SplitEmployeeIds(@EmployeeId, ','))))