Display number of orders handled by each employee for each year.

First, use a CTE to hold employee Id$,$ order Id$,$ and the year the order was handled. Next, from the CTE

select employee Id$,$ year, and count number of orders in that year.

SQL Example $3\mathrm{.}1$ Statement

WITH EmployeeSales $($EmployeeId$,$ EmployeName, OrderId, OrderYear$)$ AS

SELECT E$.$ EmployeeId, E$.$ FirstName $+\text{'}\text{'}+$ LastName, O$.$OrderId, YEAR $($O$.$ OrderDate$)$

FROM HR$.$ Employees AS E JOIN Sales. Orders AS $0$

ON E$.$EmployeeId $=$ O$.$ EmployeeId

SELECT EmployeeId, EmployeName, COUNT$($OrderId$)$ AS "Number of Orders", OrderYear

FROM EmployeeSales

GROUP BY EmployeeId, EmployeName, OrderYear

ORDER BY OrderYear;