As you're reading all the rows from YTD, I guess a single scan should perform better.

Be sure not to get lost on what this is doing. Since you're using 2008, there's no last_value function available, so I had to simulate it (kind of).

DECLARE @Date date = '6/15/15'

SELECT MAX( CASE WHEN CheckDate = @Date THEN Id END) Id,

EmployeeID ,

MAX( CASE WHEN CheckDate = @Date THEN CheckDate END) CheckDate,

MAX( CASE WHEN CheckDate = @Date THEN [Units] END) [Units],

SUM( CASE WHEN CheckDate >= DATEADD( MM, DATEDIFF(MM, 0, @Date),0)

AND CheckDate <= @Date THEN [Units] END) [MTD Units],

SUM( CASE WHEN CheckDate >= DATEADD( YY, DATEDIFF(YY, 0, @Date),0)

AND CheckDate <= @Date THEN [Units] END) [YTD Units],

Rate,

MAX( CASE WHEN CheckDate = @Date THEN [Amount] END) [Amount],

SUM( CASE WHEN CheckDate >= DATEADD( MM, DATEDIFF(MM, 0, @Date),0)

AND CheckDate <= @Date THEN Amount END) [MTD Amount],

SUM( CASE WHEN CheckDate >= DATEADD( YY, DATEDIFF(YY, 0, @Date),0)

AND CheckDate <= @Date THEN Amount END) [YTD Amount],

MAX( CASE WHEN CheckDate = @Date THEN [TaxAmount] END) [TaxAmount],

MAX( CASE WHEN CheckDate = @Date THEN [DeductAmount] END) [DeductAmount],

MAX( CASE WHEN CheckDate = @Date THEN [BenefitAmount] END) [BenefitAmount]

FROM [_Test]

where CheckDate > DATEADD( YY, DATEDIFF(YY, 0, @Date),0)

AND CheckDate <= @Date

GROUP BY EmployeeID, Rate