The biggest performance factor overall is properly clustering the tables.  Presumably you always query by client (if you have clients that see multiple clients, you might need another...

I wouldn't worry about a one-time occurrence (although you should review whether or not you are on the latest SP/patch for that SQL instance).

If it happens again, then...

Oops, sorry, I should have included that part.

SELECT *
FROM sys.indexes
WHERE index_id = 47
AND object_id = OBJECT_ID('_ACTIVITY_GEN')

Great, though, that it was index #47, that...

Identity the rows can be extremely difficult.

First check to see if it's a nonclus or clus index/heap.  If the errors are in a nonclus index, simply drop and...

What you would need to do is this:

SELECT Description
FROM (
    SELECT Description, MIN(TransactionDate) AS TransactionDate
    FROM MyTable
    GROUP BY...

Absolutely get rid of the LTRIM and RTRIM.  Also, review all indexes on the tables, particularly making sure that you have the best clustered index on every (significant)...

A couple of things here:

1) The hours ranges given exclude times and allow times to overlap, which is almost certainly not what you really want.
2) Let's go...

All you need is year, since any birthday during the @service year won't count anyway:

DECLARE @Birth DATETIME = '11/30/1951'
 ,@Service DATETIME = '2016-08-26'

I wouldn't limit the values to single chars or require each value to be the next letter in the alphabet.  Instead, for example:

INSERT INTO #tbl_value(Code,Name)
...

SELECT customerID
FROM table_name
GROUP BY customerID
HAVING MAX(CASE WHEN RowID = 1 and CustomerCode = 'P' THEN 1 ELSE 0 END) = 1 AND
  MAX(CASE WHEN RowID =...

What a bizarre claim.  DISTINCT is more...

You must use "IS NOT NULL" rather than just "NOT NULL":

if @sen like 'BY%' and @fdd IS NOT NULL
...