Запрос на выбор из базы Northwind возвращающий сумму продаж каждого сотрудника за каждый год

select  Year(Orderdate) AS ' ГОД', Employees.LastName,
round (  SUM (od.Quantity*(od.UnitPrice - od.UnitPrice *od.Discount)),2)  AS ' ИТОГО ЗА ГОД',
COUNT (Orders.OrderID) AS 'Кол-во заказов'
from Order_Details od
join Orders  on  od.OrderID = Orders.orderid
right join Employees  on Orders.EmployeeID = Employees.EmployeeID
group by  Year(Orderdate), Employees.LastName
order by  Year(Orderdate),COUNT (Orders.OrderID)

USE Northwind
GO
 
SELECT dbo.Employees.FirstName, dbo.Employees.LastName, YEAR(dbo.Orders.OrderDate) AS Years, 
        SUM(dbo.[Order Details].Quantity * (dbo.[Order Details].UnitPrice - dbo.[Order Details].Discount)) AS SumMoney, 
        COUNT(dbo.Orders.OrderID) AS OrdersCount
FROM dbo.[Order Details] RIGHT OUTER JOIN
        dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID RIGHT OUTER JOIN
        dbo.Employees ON dbo.Orders.EmployeeID = dbo.Employees.EmployeeID
GROUP BY dbo.Employees.FirstName, dbo.Employees.LastName, YEAR(dbo.Orders.OrderDate)
ORDER BY dbo.Employees.LastName, YEAR(dbo.Orders.OrderDate)

INSERT INTO Employees (LastName, FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,Address,City,Region,PostalCode,Country, HomePhone, Extension)  
VALUES ('Ivanov', 'Ivan','Sales Representative','Mr',1958-01-09,1994-01-02,'Edgeham Hollow  Winchester Way','Moscow','WA',98105,'Russia','(206) 555-1189',425 )

USE Northwind
GO
set IDENTITY_INSERT Orders ON
INSERT INTO "Orders"
(OrderID,CustomerID,"EmployeeID","OrderDate","RequiredDate",
    "ShippedDate")
VALUES (11078,N'VINET',10,'7/4/1996','8/1/1996','7/16/1996')
set IDENTITY_INSERT Orders OFF
set IDENTITY_INSERT [Order Details] ON
INSERT [Order Details] (OrderID,ProductID,UnitPrice,Quantity,Discount) VALUES(11078,77,13,2,0)
set IDENTITY_INSERT [Order Details] OFF

select
 e.FirstName, e.LastName, o.y, agg.SumMoney, agg.OrdersCount
from
 dbo.Employees e cross join
 (select distinct year(OrderDate) from dbo.Orders) o(y) left join
 (
  select
   a.EmployeeID, year(a.OrderDate),
   sum(b.Quantity * (b.UnitPrice - b.Discount)),
   count(distinct a.OrderID)
  from
   dbo.Orders a join
   dbo.[Order Details] b on b.OrderID = sa.OrderID
  group by
   a.EmployeeID, year(a.OrderDate)
 ) agg(EmployeeID, y, SumMoney, OrdersCount) on agg.EmployeeID = e.EmployeeID and agg.y = o.y;

SELECT yar.y AS 'ГОД', e.FirstName, e.LastName,  ISNULL (ag.SumMoney, 0) AS 'Сумма продаж', ISNULL (ag.OrdersCount,0) AS 'Кол-во продаж'
FROM Employees e 
 CROSS JOIN 
 (
 SELECT DISTINCT year(OrderDate) FROM dbo.Orders
 ) 
 yar(y) 
 LEFT JOIN 
 (
  SELECT   o.EmployeeID, year(o.OrderDate),
   round (sum(od.Quantity * (od.UnitPrice - od.Discount)),2),
   count(DISTINCT o.OrderID)
  FROM   Orders o 
  JOIN   Order_Details od ON od.OrderID = o.OrderID
  GROUP BY  o.EmployeeID, year(o.OrderDate)
 ) 
 ag(EmployeeID, y, SumMoney,  OrdersCount) ON ag.EmployeeID = e.EmployeeID AND ag.y = yar.y
 Order By yar.y, ag.SumMoney