I'd have to see the specific code, but it appears the GROUP BY is not complete/correct:

SELECT
ID, NAME,
MAX(CASE WHEN TestType.id = 1 THEN Testing.score END) AS Reading,

More than 50% of the time, identity is the wrong clustering key, because there's another column(s) that is(are) more valuable overall as the one and only clustering key. The single...

Or this:

SELECT FirstDate, SecondDate, ExpectedResult,
ABS(DATEDIFF(year, FirstDate, SecondDate)) as CurrentMethod,
CASE WHEN SecondDate >= FirstDate THEN
  CASE WHEN (DATEPART(MONTH, SecondDate)...

As below.  Or, if you need to show blanks rather than NULLs, then:
ISNULL(CAST(MAX(CASE WHEN TestType.id = 1 THEN Testing.score END) AS varchar(4)), '') AS Reading,
etc.

[code...

You don't need an identity column on this table: it just corrupts the table design and performance (and is a moderate waste of disk and RAM as well).  There really...

I'll use the best column name for the particular data/business requirement.  If that happens to be a reserved word, so be it.  I don't go out of my way to...

Are you allowed to create a split function in the tempdb db?  Ask them.  It will make this much easier and more consistent to do.  Of course you'll need code...

You need to define a "week", that is, what day do you consider to be the start of a week?

Use the SQL start up proc to adjust the identity value back to a lesser-gap value.  You can do a MAX($IDENTITY) to get the highest current value, add 1 (or...

Yeah, you should have no problems with applying a new svc pack.

I don't believe we're saying you need to correct everything wrong with the table right now (I suspect that would be a truly big task).  But this change seems pretty...