Start fresh with a new db design!

Do a true logical design -- without NO carryover from current design and NO consideration of physical implementation (Oracle, SQL, etc.).  NONE. ...

Short answer:
You want a dynamically-generated, static trigger.  That sounds contradictory but it's not.

More detailed answer:
You must meet two critical conditions here:
1) the trigger must execute as...

Select Count(*) as XmlEventsCount,
    Max(case when x.Status = 'NOTE' THEN 'Yes' ELSE 'No' END) as SentToProvider
From TicketEvents x
Where x.RowID=1136394
Group By x.RowID

Nope, it actually doesn't.  By default SQL will make it so, but it doesn't have to be.

Wow, yeah, for sure cluster by DateSent if that's the case.  The other conditions won't matter.

Btw, the other tables I was worried about being scanned, I had more...

Yep, that's fine.  The results are interesting.

1) Do you often/very often search by a SentDate range?
2) Is a 1 year range typical for a date search? ...

First thing is to make sure you have the best clustering index on the table.  Then we'll look to see if additional indexes or code changes or other things are...

You could try using a CROSS APPLY to assign the alias name, then use that alias in the SELECT clause:

SELECT ...,
    ca1.STAGE_022_COMPLETED_DATE_IS_NULL_REV_DATE,
    [dbo].[fnc_CalcWorkDaysBetween] (ca1.STAGE_022_COMPLETED_DATE_IS_NULL_REV_DATE,...

Float doesn't have nearly 60 digits of precision, does it?

SELECT REPLACE(CAST(FORMAT(CAST('123456789012345678901234567890123456789012345678901234567890' AS float),
'### | ### | ### | ### | ### | ### | ### | ###...

Basically this:

SELECT SCandidateID
FROM dbo.CandidateSkills
WHERE Skill IN (@ssrs_skill_param)
GROUP BY SCandidateID
HAVING COUNT(*) = <number_of_values_in_skill_param>

But not sure how in SSRS to get a count of...

I say keep it simple unless you really need to do something more complex:

SELECT STUFF(
    CASE WHEN SUBSTRING(column1, 1, 3) = '000'...