payment_ref
WITH first_reversal AS (
SELECT
Payment_Ref,
Payment_Log,
Log_Date,
activity_type,
Source,
[User], -- 'User' is a reserved word, so we use square brackets
amount,
credit_or_debit,
Comment,
ROW_NUMBER() OVER (PARTITION BY Payment_Ref ORDER BY Log_Date) AS rev_rank
FROM test
WHERE activity_type = 'Payment_Rev' AND Payment_Log LIKE '%-Reverse'
)
-- Select the first reversal and its corresponding 'Pay' log
SELECT
pr.Payment_Ref,
REPLACE(pr.Payment_Log, '-Reverse', '-Pay') AS Payment_Log,
t.Log_Date,
t.activity_type,
t.Source,
t.[User],
t.amount,
t.credit_or_debit,
t.Comment
FROM first_reversal pr
JOIN test t ON pr.Payment_Ref = t.Payment_Ref
AND REPLACE(pr.Payment_Log, '-Reverse', '-Pay') = t.Payment_Log
WHERE pr.rev_rank = 1;
Output