SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();
Msg 195 Level 15 State 10 Line 1
'version' is not a recognized built-in function name.

-- Create the table
CREATE TABLE test (
    Payment_Ref INT,
    Payment_Log VARCHAR(50),
    Log_Date DATETIME,
    activity_type VARCHAR(50),
    Source VARCHAR(50),
    [User] VARCHAR(50),  -- 'User' is a reserved keyword in SQL Server, so we need to wrap it in square brackets.
    amount DECIMAL(10,2),
    credit_or_debit VARCHAR(10),
    Comment VARCHAR(255)
);

-- Insert sample data
INSERT INTO test (Payment_Ref, Payment_Log, Log_Date, activity_type, Source, [User], amount, credit_or_debit, Comment)
VALUES
(123156, '165151-Pay', '2024-05-10 06:19:00', 'Payment', 'Post', 'Ariba', 210.72, 'credit', ''),
(123156, '46546-Pay', '2024-05-10 12:24:00', 'Payment', 'Post', 'Joelb', 10.86, 'credit', ''),
(123156, '465444-Pay', '2024-05-10 12:25:00', 'Payment', 'Post', 'Joelb', 572.57, 'credit', ''),
(123156, '465444-Reverse', '2024-07-27 05:10:00', 'Payment_Rev', 'Reverse', 'katys', 572.57, 'debit', 'Joelb'),
(123156, '44465-Pay', '2024-07-27 05:13:00', 'Payment', 'Post', 'katys', 22.67, 'credit', ''),
(123156, '46846-Pay', '2024-07-27 05:14:00', 'Payment', 'Post', 'Ariba', 360.10, 'credit', ''),
(123156, '46846-Reverse', '2024-07-27 05:15:00', 'Payment_Rev', 'Post', 'katys', 360.10, 'credit', ''),
(164645, '565464-Pay', '2024-07-17 06:55:00', 'Payment', 'Post', 'Ariba', 242.80, 'credit', ''),
(164645, '565464-Reverse', '2024-07-17 21:37:00', 'Payment_Rev', 'Reverse', 'Joelb', 242.80, 'debit', 'Ariba'),
(164645, '454464-Pay', '2024-07-17 21:38:00', 'Payment', 'Post', 'Joelb', 210.68, 'credit', ''),
(164645, '545454-Pay', '2024-07-18 01:40:00', 'Payment', 'Post', 'paula', 32.12, 'credit', ''),
(164645, '646444-Pay', '2024-07-18 01:43:00', 'Payment', 'Post', 'paula', 50.11, 'credit', '');

12 rows affected

select * from test;


Payment_Ref
Payment_Log
Log_Date
activity_type
Source
User
amount
credit_or_debit
Comment


123156
165151-Pay
2024-05-10 06:19:00.000
Payment
Post
Ariba
210.72
credit


123156
46546-Pay
2024-05-10 12:24:00.000
Payment
Post
Joelb
10.86
credit


123156
465444-Pay
2024-05-10 12:25:00.000
Payment
Post
Joelb
572.57
credit


123156
465444-Reverse
2024-07-27 05:10:00.000
Payment_Rev
Reverse
katys
572.57
debit
Joelb

123156
44465-Pay
2024-07-27 05:13:00.000
Payment
Post
katys
22.67
credit


123156
46846-Pay
2024-07-27 05:14:00.000
Payment
Post
Ariba
360.10
credit


123156
46846-Reverse
2024-07-27 05:15:00.000
Payment_Rev
Post
katys
360.10
credit


164645
565464-Pay
2024-07-17 06:55:00.000
Payment
Post
Ariba
242.80
credit


164645
565464-Reverse
2024-07-17 21:37:00.000
Payment_Rev
Reverse
Joelb
242.80
debit
Ariba

164645
454464-Pay
2024-07-17 21:38:00.000
Payment
Post
Joelb
210.68
credit


164645
545454-Pay
2024-07-18 01:40:00.000
Payment
Post
paula
32.12
credit


164645
646444-Pay
2024-07-18 01:43:00.000
Payment
Post
paula
50.11
credit



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;


Payment_Ref
Payment_Log
Log_Date
activity_type
Source
User
amount
credit_or_debit
Comment


123156
465444-Pay
2024-05-10 12:25:00.000
Payment
Post
Joelb
572.57
credit


164645
565464-Pay
2024-07-17 06:55:00.000
Payment
Post
Ariba
242.80
credit

Payment_Ref	Payment_Log	Log_Date	activity_type	Source	User	amount	credit_or_debit	Comment
123156	165151-Pay	2024-05-10 06:19:00.000	Payment	Post	Ariba	210.72	credit
123156	46546-Pay	2024-05-10 12:24:00.000	Payment	Post	Joelb	10.86	credit
123156	465444-Pay	2024-05-10 12:25:00.000	Payment	Post	Joelb	572.57	credit
123156	465444-Reverse	2024-07-27 05:10:00.000	Payment_Rev	Reverse	katys	572.57	debit	Joelb
123156	44465-Pay	2024-07-27 05:13:00.000	Payment	Post	katys	22.67	credit
123156	46846-Pay	2024-07-27 05:14:00.000	Payment	Post	Ariba	360.10	credit
123156	46846-Reverse	2024-07-27 05:15:00.000	Payment_Rev	Post	katys	360.10	credit
164645	565464-Pay	2024-07-17 06:55:00.000	Payment	Post	Ariba	242.80	credit
164645	565464-Reverse	2024-07-17 21:37:00.000	Payment_Rev	Reverse	Joelb	242.80	debit	Ariba
164645	454464-Pay	2024-07-17 21:38:00.000	Payment	Post	Joelb	210.68	credit
164645	545454-Pay	2024-07-18 01:40:00.000	Payment	Post	paula	32.12	credit
164645	646444-Pay	2024-07-18 01:43:00.000	Payment	Post	paula	50.11	credit