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
...

Also, to reduce errors and potential errors, don't code dates in an ambiguous format, i.e.,
does '11/6/2010' represent Nov 6 or Jun 11??
or waste space with formatting chars....

Look for articles / books by Itzik Ben-Gan.  Yes, a book(s) will cost you a little money, but it will be worth it!