I'm still not 100% sure of your rules, but hopefully if needed you can adjust this to get what you need:

SELECT
    jec.main, jec.sub,


select
    right(space(12)+convert(varchar(20),cast([User_Info_Currency_1] as money),1), 12) as [Replacement Cost]
from (
values(
523.88000),(
60689.76000),(
48860.26000),(
77239.26000),(
132227.26000),(
65668.18000),(
71523.34000)
) as EQSA0345(User_Info_Currency_1)

Reforrmat  the SSICat data and not the ep data, since there should be far fewer SSICat rows.

How is the EncounterProc table clustered?

Hopefully you don't actually need...

SELECT
b.Period AS B_Period,
a1.Period AS A_period,
a1.SomeValue
FROM #TableB b
OUTER APPLY (
    SELECT TOP (1) a.*
    FROM #TableA a
   ...

Cluster the [SearchSuggest_QGram_tbl] on [nameCacheId] rather than on gram.

SELECT s
    s2
    ,TRY_CAST(s2 AS DATE) AS ScottPDate
FROM @t
CROSS APPLY (
    SELECT LTRIM(RTRIM(s)) AS s1
) AS ca1
CROSS...

I'd add the standard "nocount" setting, but other than that it looks good:

alter trigger trg_up_in_changeflag
on example_table
after update, insert

as

If you're on SQL 2016 (or even SQL 2012), just use TRY_CAST:


SELECT s
,TRY_CAST(s AS date) AS MyDate
FROM @t