Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE opr_operation (host_date, sttl_amount, is_reversal, mcc, msg_type) AS
  SELECT SYSDATE, 29999, 0, '6011', 'MSGTPRES' FROM DUAL UNION ALL
  SELECT SYSDATE - INTERVAL '1' SECOND, 20000, 0, '6011', 'MSGTPRES' FROM DUAL;
2 rows affected

SELECT COUNT (STTL_AMOUNT) as "COUNT",
       SUM(CASE WHEN IS_REVERSAL = 1 THEN 0 ELSE STTL_AMOUNT/100 END)
         as "Settlement Amount",
       HOST_DATE as "Settlement Date"
FROM   OPR_OPERATION
WHERE  MCC = '6011'
AND    MSG_TYPE ='MSGTPRES'
AND    HOST_DATE >= TRUNC( SYSDATE )
GROUP BY HOST_DATE;


COUNT
Settlement Amount
Settlement Date


1
299.99
2024-05-24 21:23:20

1
200
2024-05-24 21:23:19


SELECT COUNT (STTL_AMOUNT) as "COUNT",
       SUM(CASE WHEN IS_REVERSAL = 1 THEN 0 ELSE STTL_AMOUNT END/100)
         as "Settlement Amount",
       HOST_DATE as "Settlement Date"
FROM   OPR_OPERATION
WHERE  MCC = '6011'
AND    MSG_TYPE ='MSGTPRES'
AND    HOST_DATE >= TRUNC( SYSDATE )
GROUP BY HOST_DATE;


COUNT
Settlement Amount
Settlement Date


1
299.99
2024-05-24 21:23:20

1
200
2024-05-24 21:23:19


SELECT COUNT (STTL_AMOUNT) as "COUNT",
       SUM(CASE WHEN IS_REVERSAL = 1 THEN 0 ELSE STTL_AMOUNT END)/100
         as "Settlement Amount",
       HOST_DATE as "Settlement Date"
FROM   OPR_OPERATION
WHERE  MCC = '6011'
AND    MSG_TYPE ='MSGTPRES'
AND    HOST_DATE >= TRUNC( SYSDATE )
GROUP BY HOST_DATE;


COUNT
Settlement Amount
Settlement Date


1
299.99
2024-05-24 21:23:20

1
200
2024-05-24 21:23:19


SELECT COUNT (STTL_AMOUNT) as "COUNT",
       TO_CHAR(
         SUM(CASE WHEN IS_REVERSAL = 1 THEN 0 ELSE STTL_AMOUNT/100 END),
         'fm99999999999999999990.00'
       ) as "Settlement Amount",
       HOST_DATE as "Settlement Date"
FROM   OPR_OPERATION
WHERE  MCC = '6011'
AND    MSG_TYPE ='MSGTPRES'
AND    HOST_DATE >= TRUNC( SYSDATE )
GROUP BY HOST_DATE;


COUNT
Settlement Amount
Settlement Date


1
299.99
2024-05-24 21:23:20

1
200.00
2024-05-24 21:23:19