| SELECT a.EmpNo, RTrim(a.FirstName) + ' ' + RTrim(a.LastName) AS EmpName, SUM(b.DocTotal) AS STotal FROM Employees AS a LEFT JOIN Invoice AS b ON a.EmpNo = b.SlpCode WHERE a.DeptNo = 4 AND EXISTS (SELECT * FROM Invoice AS t1 INNER JOIN Employees AS t2 ON t1.SlpCode = t2.EmpNo WHERE t2.DeptNo = 4 AND YEAR(t1.docdate)=1999) GROUP BY a.EmpNo, RTrim(a.FirstName) + ' ' + RTrim(a.LastName); |