Couple of points about the query.

The query updates the table with values from a copy of the same table grouped by unique key

TimeId, [GeoId], [XValue].

Every record with the same...

Try to separate GLL together with its CROSS APPLY and GLS together with its CROSS APLY into different subqueries (in form of either der. I've tables or CTEs) and join...

The query contains 3 instances of #OUT_DBD__calculateMeasureByAlgorithm_Q_WNF_AFFECTEDAREA_CumulativeCasesAllSeasons which are joined to [DM_Ref].[ref].[Time] 2 times.

I suspect there is a better way to retrieve required data.

I also suspect the temp tables don't...

Every compiler is a code generator.

A generator of Machine code - aka Assembler.

Telling a machine what to do and let it find a way to execute the request - it's...

"eventually we'll tell systems what we want and they'll build algorithms"

Does not quite work even with actual people. 🙂

In fact, they build algorithms, but usually it's not exactly what you...

This one should return the expected result.
   SELECT *,
        COALESCE(
            RIGHT([StringBeforeLast%], NULLIF(patindex('%[^0-9,.]%', REVERSE([StringBeforeLast%])),0) -1),
            [StringBeforeLast%],
            '') + '%' ActualOutcome
    FROM (
        select S.description
        ,datalength(S.description) - nullif(CHARINDEX('%', reverse(S.description)),...

Also,
row #2 contains the string '1'.
No '%' in there.
Row #2 in #expectedoutcome contains '1%'
How does it correlate?

Whatever kind of identity you choose to use - it's the worst possible choice for a clustered key.

The best candidate for a clustered index is a column (set...