Agree with Luis, except even more strongly:
We must see the DDL for the tables, including all indexes.
It would also be nice to see usage stats for those indexes,...

Also, consider clustering dbo.MyTable on (ValidUntil, CreatedOn) or adding ValidUntil to the nonclus index. [You don't have to cluster tables by identity, and it can actually be quite harmful to...

If you (almost) always join on all the keys, then yes, cluster on all of them.

You really should strongly consider defining the clus index before loading the table...

Given that a matched pickup and dropoff won't change in the future, when a dropoff gets entered, have a trigger link it to its pickup (store the pickup key(s) in...

I'd check column separately rather than adding them, just in case negative amounts could appear now, or somehow do appear later:

WHERE CE.CLOSING_CREDITS <> 0 AND CE.CLOSING_DEBITS <> 0

Also, you want to avoid BETWEEN when dealing with dates / times.
I have heard this often but I have never seen any proof as to why. I am...

SELECT TOP (2) IDUser, Score, Name
FROM (
  SELECT U.IDUser, U.Score, U.RecordDt, S2.Name,
      ROW_NUMBER() OVER(PARTITION BY U.IDUser, S2.Name ORDER BY U.RecordDt DESC) AS...

I think something like below is much easier to follow.  Also, you want to avoid BETWEEN when dealing with dates / times.

I can't tell for sure if the...

Or, better yet, just go back to properly using 1NF in the table and store all values as atomic, i.e., individual columns.  The computed column should be the concatenated value,...

1) No, afaik.
2) Yes, [H]HHHMMSS.
3) I've no longer got CDC jobs really, but I don't remember seeing that phenomenon when I did, very interesting.

WHERE/ON SUBSTRING(string, CHARINDEX('-', string) + 1, 5) = ...

To get the avg total time, you could do this:

DATEADD(SECOND, SUM(DATEDIFF(SECOND, 0, TotalTime)) / COUNT(TotalTime), 0)

Here's a rewrite of the first query (before the UNION ALL).  I don't have time to do both but they look very similar.  The second two subqueries can be combined...