Yes, as single SQL statement can be written but I would be very concerned about resource utilization (e.g. the SQL would run like a pig).

Please be sure to read Jeff Moden's article titled "Hidden RBAR: Triangular Joins" at http://www.sqlservercentral.com/articles/T-SQL/61539/

This SQL ranks each staff member's review for a specific year and is a hidden RBAR. With SQL Server 2005/2008, better performance can be obtained using the rank window function.

select#tTestData.StaffName

,#tTestData.ReviewDate

,count(*)as ReviewOrder

from#tTestData

join#tTestDataas PriorReview

on PriorReview.StaffName = #tTestData.StaffName

and PriorReview.ReviewDate <= #tTestData.ReviewDate

whereyear(#tTestData.ReviewDate) = @iyear - 2

andyear(PriorReview.ReviewDate) = @iyear - 2

group by #tTestData.StaffName

,#tTestData.ReviewDate

This SQL determines the maximum number of reviews for a staff member in any of the past three years:

select StaffName

,CASE WHEN Year0Cnt > Year1Cnt and Year1Cnt > Year0Cntthen Year0Cnt

when Year1Cnt > Year0Cnt then Year1Cnt

else Year0Cnt

end as StaffReviewCnt

from(

Select StaffName

, SUM( case when year(ReviewDate) = @iyear - 2 then 1 else 0 end ) as Year2Cnt

, SUM( case when year(ReviewDate) = @iyear - 1 then 1 else 0 end ) as Year1Cnt

, SUM( case when year(ReviewDate) = @iyear - 0 then 1 else 0 end ) as Year0Cnt

From #tTestData

group By StaffName

) as StaffYearlyReviews

This SQL enumerates each of the possible ranks up to four in any given year. A auxiliary numbers table would be a better solution - see http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

select 1 as RowNum union all select 2 union all select 3 union all select 4

Finally, combine all the individual SQL statements:

declare@iYear smallint

set@iyear = 2008

selectStaffReviews.StaffName

,Review2.ReviewDate

,Review1.ReviewDate

,Review0.ReviewDate

from(select StaffName

,CASE WHEN Year0Cnt > Year1Cnt and Year1Cnt > Year0Cntthen Year0Cnt

when Year1Cnt > Year0Cnt then Year1Cnt

else Year0Cnt

end as StaffReviewCnt

from(

Select StaffName

, SUM( case when year(ReviewDate) = @iyear - 2 then 1 else 0 end ) as Year2Cnt

, SUM( case when year(ReviewDate) = @iyear - 1 then 1 else 0 end ) as Year1Cnt

, SUM( case when year(ReviewDate) = @iyear - 0 then 1 else 0 end ) as Year0Cnt

From #tTestData

group By StaffName

) as StaffYearlyReviews

) as StaffReviews

join(select 1 as RowNum union all select 2 union all select 3 union all select 4 )

as ReviewRow

on ReviewRow.RowNum between 1 and StaffReviews.StaffReviewCnt

left outer join

(select#tTestData.StaffName

,#tTestData.ReviewDate

,count(*)as ReviewOrder

from#tTestData

join#tTestDataas PriorReview

on PriorReview.StaffName = #tTestData.StaffName

and PriorReview.ReviewDate <= #tTestData.ReviewDate

whereyear(#tTestData.ReviewDate) = @iyear - 2

andyear(PriorReview.ReviewDate) = @iyear - 2

group by #tTestData.StaffName

,#tTestData.ReviewDate

) as Review2

on Review2.StaffName= StaffReviews.StaffName

and Review2.ReviewOrder = ReviewRow.RowNum

left outer join

(select#tTestData.StaffName

,#tTestData.ReviewDate

,count(*)as ReviewOrder

from#tTestData

join#tTestDataas PriorReview

on PriorReview.StaffName = #tTestData.StaffName

and PriorReview.ReviewDate <= #tTestData.ReviewDate

whereyear(#tTestData.ReviewDate) = @iyear - 1

andyear(PriorReview.ReviewDate) = @iyear - 1

group by #tTestData.StaffName

,#tTestData.ReviewDate

) as Review1

on Review1.StaffName= StaffReviews.StaffName

and Review1.ReviewOrder = ReviewRow.RowNum

left outer join

(select#tTestData.StaffName

,#tTestData.ReviewDate

,count(*)as ReviewOrder

from#tTestData

join#tTestDataas PriorReview

on PriorReview.StaffName = #tTestData.StaffName

and PriorReview.ReviewDate <= #tTestData.ReviewDate

whereyear(#tTestData.ReviewDate) = @iyear - 0

andyear(PriorReview.ReviewDate) = @iyear - 0

group by #tTestData.StaffName

,#tTestData.ReviewDate

) as Review0

on Review0.StaffName= StaffReviews.StaffName

and Review0.ReviewOrder = ReviewRow.RowNum

The result set is

Blow, Joe2006-03-23 00:00:002007-03-13 00:00:002008-01-24 00:00:00

Blow, Joe2006-07-18 00:00:002007-06-28 00:00:00NULL

Blow, JoeNULL2007-12-21 00:00:00NULL

Doe, Jane2006-05-16 00:00:002007-03-12 00:00:002008-01-22 00:00:00

Doe, Jane2006-11-19 00:00:002007-06-11 00:00:00NULL

Doe, JaneNULL2007-12-16 00:00:00NULL

Dunno, I2006-12-10 00:00:002007-07-05 00:00:00NULL

Dunno, INULL2007-12-18 00:00:00NULL