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

This may help you.

John

Your subquery SELECT Max(D.sent_date)... needs a WHERE clause.

John

What happens if you move the two lines you added between the penultimate and the final parentheses?

John

Check what, if anything, is preventing reuse in the log file like this:
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyDatabase';

John