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
SQL = Scarcely Qualifies as a Language