Your article is an interesting insight as to how you solved the problem and how to use CTEs. However, I’m not convinced it produces the correct results, given your data. Your final query returns the result for R & P suns with the expenses date of 28th Feb 2009, whereas your data has an entry for the 10th July 2009. Surely this is the row that should be returned? This is due to the data in the Quarter column not corresponding to the ReportMonth. In fact the data seems a bit dodgy, so maybe it is just your test data.

Assuming the Quater and ReportWeek are irrelevant in the query and the FinancialYear, ReportMonth and ReportDay can give you the date of the expense, then I think that the following query using a CROSS APPLY is simpler to understand than using a CTE IMHO.

SELECT c.ContractorName AS 'Contractor Name'

, CONVERT(VARCHAR(15),e.ExpenseDate, 101) AS 'Last Expense Date'

, '$' + CONVERT(VARCHAR(20),e.Expense,1) AS 'Expense'

FROM tmpContractor AS c

CROSS APPLY

(SELECT TOP 1 se.ContractorId

, se.Expense

, DATEADD(yy, (se.FiscalYear - 1900),DATEADD(mm, se.ReportMonth - 1,DATEADD(dd, se.ReportDay -1,0))) AS 'ExpenseDate'

FROM tmpBusinessExpense AS se

WHERE se.ContractorID = c.ContractorID

ORDER BY DATEADD(yy, (se.FiscalYear - 1900),DATEADD(mm, se.ReportMonth - 1,DATEADD(dd, se.ReportDay -1,0))) DESC

) AS e

ORDER BY 1