WITH 
OrdersFor744 AS (
SELECT SalesOrderID FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (SELECT SalesOrderID FROM
Sales.SalesOrderDetail WHERE ProductID=744)
),
OrderTotalsFor744 AS (
SELECT SalesOrderID, SUM(UnitPrice * OrderQty) AS Total 
From Sales.SalesOrderDetail
WHERE SalesOrderID in (SELECT SalesOrderID FROM OrdersFor744)
GROUP BY SalesOrderID
),
AvgOrdersFor744 AS (
SELECT Avg(Total) AS Average FROM OrderTotalsFor744
),
AboveAvgOrdersFor744 AS (
SELECT OT.SalesOrderID FROM OrderTotalsFor744 OT
CROSS APPLY AvgOrdersFor744 AS A WHERE A.Average < OT.Total 
),
MakeCall AS (
SELECT SalesPersonID FROM Sales.SalesOrderHeader
WHERE SalesOrderID in 
(SELECT SalesOrderID FROM AboveAvgOrdersFor744)
)
SELECT SalesPersonID,
CASE
WHEN SalesPersonID in (SELECT SalesPersonID FROM MakeCall) 
THEN 'Make Call!'
ELSE 'Relax'
END
FROM Sales.SalesPerson;
a
b
c
d
e
