Sounds like you have a lot of unused indexes, yes.  But you need to consider how long it is since the DMVs were reset (usually the last time SQL Server...

Number the rows in the StartPoint CTE using ROW_NUMBER().  Then, instead of using LEAD in the StartPointsandNext CTE, join StartPoint to itself on RowNo = RowNo + 1.

John

DECLARE @Contents varchar(4000)
SET @Contents =
'abcdef 01/01/2018 08:15:13 This is a note , this is a note , this is a note , this is a...

Is it possible that "this is a note" will contain six letters followed by a date and time?

John

Sorry, but you've confused me.  First you said the source is SQL, now you're saying it's .txt.  But if it's .txt, just bring it into a staging table - as...

WITH Grouped AS (
    SELECT
         Account
    ,    Date
    ,    SUM(Amount) OVER (PARTITION BY Account, Date) AS Amount
    ,    RecID
    FROM #MyTrans
    )
SELECT
     Account
,    Date

Please will you show us what you've already tried, because it's not clear where you're stuck?

The best advice is to change the design - there's no way you...

Do it exactly as Drew showed you, but in the ZATS column, you only need ZATS = b.HTS_NUMBER - you don't need the subquery there.

John

This should get you started.  It relies on INs and OUTs being properly in sequence.  If they're not, you'll need to deal with that first.

WITH InandOut...

Yikes!  A correlated subquery.  You could try putting this query into a CTE and joining to it on Part_Number = composite_Part and RowNo = 1.

SELECT
 ...

Have you tried AT TIME ZONE instead?

John