[SOLVED] Summing up the result based on historical data

Summing up the result based on historical data

I need to do a summation of a result based on account and transaction date. I have the following result:

 ACCOUNT_ID     TRANSACTION_DATE    AMOUNT
  581            05-SEP-23         309.32
  581            08-SEP-23         1863.76
  581            15-SEP-23         0.26
  581            21-SEP-23         23.17

with transaction_tbl 
 AS (

   select  581 ACCOUNT_ID,  TO_DATE('05-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    309.32 AMOUNT from dual
union all
select  581 ACCOUNT_ID,  TO_DATE('08-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    1863.76 AMOUNT from dual
union all
select  581 ACCOUNT_ID,  TO_DATE('15-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    0.26 AMOUNT from dual
union all
select  581 ACCOUNT_ID,  TO_DATE('21-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    23.17 AMOUNT from dual
)
SELECT
    ACCOUNT_ID,TRANSACTION_DATE,AMOUNT
FROM
    transaction_tbl
    --where TRANSACTION_DATE < asofdate ;

I have the following case that I was unable to process.

Case when TRANSACTION_DATE < asofdate TO_DATE('06-09-2023', 'DD-MM-YYYY') expected result;

581 05-SEP-23   309.32

Case when TRANSACTION_DATE < asofdate TO_DATE('09-09-2023', 'DD-MM-YYYY') expected result;

581 08-SEP-23   2173.08

Case when TRANSACTION_DATE < asofdate TO_DATE('18-09-2023', 'DD-MM-YYYY') expected result;

581 15-SEP-23   2173.34

Case when TRANSACTION_DATE < asofdate TO_DATE('22-09-2023', 'DD-MM-YYYY') expected result;

581 21-SEP-23   2196.51

The idea is that I need to recalculate the result based on previous data for a date that exists as a transaction.

Solution

You can use use aggregate functions together wir an GROUP BY clause like this:

with transaction_tbl
         AS (

        select  581 ACCOUNT_ID,  TO_DATE('05-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    309.32 AMOUNT
        union all
        select  581 ACCOUNT_ID,  TO_DATE('08-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    1863.76 AMOUNT
        union all
        select  581 ACCOUNT_ID,  TO_DATE('15-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    0.26 AMOUNT
        union all
        select  581 ACCOUNT_ID,  TO_DATE('21-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE,    23.17 AMOUNT
    )
SELECT
    ACCOUNT_ID, max(TRANSACTION_DATE), sum(AMOUNT)
FROM
    transaction_tbl
where TRANSACTION_DATE < TO_DATE('22-09-2023', 'DD-MM-YYYY')
group by 1;