DECLARE @ps_StartDate DATETIME = '20190101' , @ps_EndDate DATETIME = '20191224' , @ps_COMPANYNO VARCHAR(20) = '01' SET @ps_EndDate = DATEADD(dd, 1, @ps_EndDate) SELECT CASE WHEN @ps_COMPANYNO IN ('01') THEN 'YS45J146' ELSE 'YS45J147' END AS 'Client ID' ,e.EMPNO AS 'WORKER ID' ,'' AS 'Org' ,'' AS 'Job Number' ,'Regular' AS 'Pay Component' ,'' AS 'Rate' ,'' AS 'Rate Number' ,SUM(t.TIMEBILLED + (ISNULL(ehrs.AllocationDays, 0) * 24)) AS 'Hours' ,'' AS 'Units' ,'' AS 'Line Date' ,'' AS 'Amount' ,'' AS 'Check Seq Number' ,'' AS 'Override State' ,'' AS 'Override Local' ,'' AS 'Override Local Jurisdiction' ,'' AS 'Labor Assignment' FROM TIMEBILL t (NOLOCK) INNER JOIN EMPLOYEE e (NOLOCK) ON t.USERID = e.USERID LEFT JOIN dbo.EmpHRSchedule ehrs (NOLOCK) ON (e.EmployeeNo = ehrs.EmployeeNo) WHERE t.PRTID = 'P' AND t.DATE >= @ps_StartDate AND t.DATE < @ps_EndDate AND ehrs.STARTDATE >= @ps_StartDate AND ehrs.STARTDATE < @ps_EndDate AND e.COMPANYNO = @ps_COMPANYNO AND t.SUBCAT <> 'LUNCH' AND t.WAGETYPE <> 'OVERTIME' AND e.SALARYUNIT = 'HOURLY' --AND ehrs.Type = 'HOLIDAY' GROUP BY e.EMPNO
I’m trying to add the dbo.EmpHRSchedule.Type = ‘HOLIDAY’ entries to the Hours column only if they exist. To my understanding, a LEFT JOIN would only return entries if they match, and return NULL for non-matches. When I uncomment the AND ehrs.Type = 'HOLIDAY'
line, no results are returned at all.
Why is this the case? Is there another way I should be going about this to sum the dbo.Timebill.Timebilled and dbo.EmpHRSchedule.AllocationDays (if they exist)?
Advertisement
Answer
When you include:
AND ehrs.Type = 'HOLIDAY'
in the WHERE
clause, you actually get an INNER
join instead of a LEFT
join because you get only the matching rows from the table ers
and not any of the unmatched rows for which ehrs.Type
would be NULL
.
What you can do is move the condition to the ON
clause of the LEFT
join:
LEFT JOIN dbo.EmpHRSchedule ehrs (NOLOCK) ON (e.EmployeeNo = ehrs.EmployeeNo) AND ehrs.Type = 'HOLIDAY'
and you could also include the other conditions for the same table:
LEFT JOIN dbo.EmpHRSchedule ehrs (NOLOCK) ON (e.EmployeeNo = ehrs.EmployeeNo) AND ehrs.Type = 'HOLIDAY' AND ehrs.STARTDATE >= @ps_StartDate AND ehrs.STARTDATE < @ps_EndDate