For some reason the data condition in the where clause is being ignored - any suggestions?
SELECT D.[Date],
D.Year,
D.MonthName,
D.WeekOfYear,
E.bu_name,
E.emp_mgr,
E.emp_sup,
E.emp_name,
E.emp_jde,
C.calls,
s.sales
FROM DateTable AS D
CROSS JOIN EmployeeTable AS E
LEFT JOIN (SELECT CAST([start_date] AS DATE) AS call_date,
[agent_no] AS agent_id,
SUM(CASE WHEN [skill_name] LIKE '5700 Sales l%'
AND [Agent_Time] != '0' THEN 1
ELSE 0
END) AS calls
FROM CallTable
GROUP BY CAST([start_date] AS DATE),
[agent_no]
) AS C ON D.[Date] = C.call_date
AND E.emp_vcc = C.agent_id
LEFT JOIN (SELECT [AC#DTE_dt] AS sale_date,
[EMPJDENUM] AS emp_jde,
SUM(CASE WHEN [CHANNEL] = 'I'
AND ([ICGCD2] = 'L'
OR [ICGCD2] = 'H')
AND [ITMQTY] > 3
AND [EMPBUNCOD] IN ('5044','5077') THEN 1
ELSE 0
END) AS sales
FROM SalesTable
GROUP BY [AC#DTE_dt],
[EMPJDENUM]
) AS S ON D.Date = s.sale_date
AND E.emp_jde = S.emp_jde
WHERE c.calls > 0
OR S.sales > 0
AND d.Year = '2016'