SQL Server 2022

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

CREATE TABLE OverPunch_Values (
    Overpunch CHAR,
    Numeric INT,
    Sign INT,
    VarCharSign INT
)
INSERT OverPunch_Values
VALUES
    ('}', '0', -1, -1),
    ('J', '1', -1, -1),
    ('K', '2', -1, -1),
    ('L', '3', -1, -1),
    ('M', '4', -1, -1),
    ('N', '5', -1, -1),
    ('O', '6', -1, -1),
    ('P', '7', -1, -1),
    ('Q', '8', -1, -1),
    ('R', '9', -1, -1),
    ('{', '0', 0, 1),
    ('A', '1', 1, 1),
    ('B', '2', 1, 1),
    ('C', '3', 1, 1),
    ('D', '4', 1, 1),
    ('E', '5', 1, 1),
    ('F', '6', 1, 1),
    ('G', '7', 1, 1),
    ('H', '8', 1, 1),
    ('I', '9', 1, 1),
    ('{', '0', 0, 0)
21 rows affected

CREATE TABLE Data (
    ID INT,
    CustomerPaid NUMERIC(12,0),
    CustomerSaved NUMERIC(12,0)
)
INSERT Data
VALUES
    (1, 0, -0), -- expect '00000000000{', '000000000000{' -- negative zero not supported
    (2, 1, -1), -- expect '00000000000A', '000000000000J'
    (3, 222, -222), -- expect '00000000055E', '000000000055N'  --  Sums = +555, -555
    (3, 333, -333), -- expect '00000000055E', '000000000055N'
    (4, 123456789012, -123456789012), -- expect '12345678901B', '12345678901K'
    (5, 999999999999, -999999999999)  -- expect '99999999999I', '99999999999R'
6 rows affected

WITH SumData AS (
    SELECT
        ID,
        SUM(CustomerPaid) AS SumCustomerPaid,
        SUM(CustomerSaved) AS SumCustomerSaved
    FROM Data
    GROUP BY ID
)
SELECT 
    ID
    , SumCustomerPaid
    , SumCustomerSaved
    , LEFT(RIGHT(CONCAT('000000000000', ABS(SumCustomerPaid)), 12), 11) + o1.overpunch AS EncodedCustomerPaid
    , LEFT(RIGHT(CONCAT('000000000000', ABS(SumCustomerSaved)), 12), 11) + o2.overpunch AS EncodedCustomerSaved
FROM SumData

LEFT JOIN OverPunch_Values o1
  ON RIGHT(SumCustomerPaid, 1) = o1.numeric AND SIGN(SumCustomerPaid) = o1.VarCharSign

LEFT JOIN OverPunch_Values o2
  ON RIGHT(SumCustomerSaved, 1) = o2.numeric AND SIGN(SumCustomerSaved) = o2.VarCharSign


ID
SumCustomerPaid
SumCustomerSaved
EncodedCustomerPaid
EncodedCustomerSaved


1
0
0
00000000000{
00000000000{

2
1
-1
00000000000A
00000000000J

3
555
-555
00000000055E
00000000055N

4
123456789012
-123456789012
12345678901B
12345678901K

5
999999999999
-999999999999
99999999999I
99999999999R

ID	SumCustomerPaid	SumCustomerSaved	EncodedCustomerPaid	EncodedCustomerSaved
1	0	0	00000000000{	00000000000{
2	1	-1	00000000000A	00000000000J
3	555	-555	00000000055E	00000000055N
4	123456789012	-123456789012	12345678901B	12345678901K
5	999999999999	-999999999999	99999999999I	99999999999R