Wow, that's a huge amount of code.  Does it do something better than the code I posted earlier?:

SELECT DT.*,
    DATEDIFF(YEAR, LowDate, HighDate) - CASE...

Yes, the "SET NOCOUNT ON" should be w/i the trigger code itself:

CREATE TRIGGER trEMCondition_INSERT
ON dbo.vEMLocationHistory
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE e

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.