By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table generalledger_mgmt (
rowsortid varchar(25),
COMPANYID varchar(5),
serial int,
TRANSDATEID varchar(25),
AMOUNTLC int,
CURRENCYCODE varchar(5)
);
insert into generalledger_mgmt values
('20211109-ICJ-565','SSE',222,'20211101', 500,'SEK'),
('20211109-ICJ-564','SSE',222,'20211105',-500,'SEK'),
('20211109-ICT-562','SSE',202,'20211001', 600,'SEK'),
('20211109-ICK-568','SSE',210,'20211201', 200,'SEK');
create table timer (id int, tim timestamp DEFAULT CURRENT_TIMESTAMP)
SELECT rowsortid
,COMPANYID
,REGEXP_SUBSTR(serial, '[^| ]+', 1, 1) as serial
,max(TRANSDATEID)
,sum(AMOUNTLC)
,max(CURRENCYCODE)
FROM generalledger_mgmt
WHERE 1 = 1
AND companyid = 'SSE'
AND transdateid >= 20211101
AND transdateid < 20220101
GROUP BY rowsortid
,COMPANYID
,REGEXP_SUBSTR(serial, '[^| ]+', 1, 1)
ORDER BY REGEXP_SUBSTR(serial, '[^| ]+', 1, 1);
| rowsortid | COMPANYID | serial | max(TRANSDATEID) | sum(AMOUNTLC) | max(CURRENCYCODE) |
|---|---|---|---|---|---|
| 20211109-ICK-568 | SSE | 210 | 20211201 | 200 | SEK |
| 20211109-ICJ-565 | SSE | 222 | 20211101 | 500 | SEK |
| 20211109-ICJ-564 | SSE | 222 | 20211105 | -500 | SEK |
SELECT rowsortid
,COMPANYID
,ABS(serial) as serial
,max(TRANSDATEID)
,sum(AMOUNTLC)
,max(CURRENCYCODE)
FROM generalledger_mgmt
WHERE 1 = 1
AND companyid = 'SSE'
AND transdateid >= 20211101
AND transdateid < 20220101
GROUP BY rowsortid
,COMPANYID
,ABS(serial)
ORDER BY ABS(serial);
| rowsortid | COMPANYID | serial | max(TRANSDATEID) | sum(AMOUNTLC) | max(CURRENCYCODE) |
|---|---|---|---|---|---|
| 20211109-ICK-568 | SSE | 210 | 20211201 | 200 | SEK |
| 20211109-ICJ-565 | SSE | 222 | 20211101 | 500 | SEK |
| 20211109-ICJ-564 | SSE | 222 | 20211105 | -500 | SEK |