Hugo Kornelis (12/6/2011)
BTW, now that I no longer have to support SQL Server 2000, I can use a better solution using a Common Table Expression:
Even in SQL Server 2000, you can write the equivalent subquery/derived table as expressed by the CTE:
SELECT
SalesRep = e.Name,
Customer = co.Name,
co.OrderDate
FROM dbo.Employees AS e
LEFT JOIN
(
SELECT
c.Name,
o.OrderDate,
c.SalesRep
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON
o.CustomerID = c.CustomerID
AND o.OrderDate > DATEADD(MONTH, -4, CURRENT_TIMESTAMP)
) AS co ON
co.SalesRep = e.EmployeeID
WHERE
e.Position = 'SalesRep';
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi