I realize that the point of this article was to compare joins to subqueries but I would have thought to use the rank function. Here is how I would have done it:
;WITH Test AS (
SELECT
CustomerName,
CalledOn,
CustomerResponse,
RANK() OVER (PARTITION BY customername ORDER BY CONVERT(DATE,CalledOn) DESC) AS Rank
FROM #TMP
)
SELECT CustomerName,CustomerResponse,CalledOn
FROM Test
WHERE Rank=1
AND CustomerResponse<>'Not Interested'
There weren't enough records in the table to know which is fastest. I am curious to know what people think of using the Rank function for these purposes instead.