CODEWITHSUNDEEP
sqloracle-databasereportingfinance
technomalogical
technomalogical

Reputation: 3002

SQL: Efficiently Applying Credits (payments) across Debits (bills)

I'm trying to generate a "cash lag" report out of an off-the-shelf financial system. I have access to the database (Oracle). The report should apply credits to debits, oldest first, and calculate the difference in days between when the bill was generated and when the payment was received. All debits and credits are stored in a single column of the financial transaction table. The applicable columns are:

Column Name  Type
-----------  -------------
AMOUNT       NUMERIC(15,2)   --debits are positive, credits are negative
FT_ID        CHAR(10)
ACCOUNT_ID   CHAR(10)
ACCEPTED_DT  DATETIME

So for a table like:

ACCOUNT_ID  FT_ID  AMOUNT   ACCEPTED_DT
---------------------------------------
         1  12345  100.00   12/01/2011
         1  12346  -75.00   12/11/2011
         1  12347  100.00   12/12/2011
         1  12348  -50.00   12/16/2011

What I would like to get back is something like the following (edit: updated to show amounts applied. I want the actual credit amount but that can be looked up based on the FT ID for the credit):

ACCOUNT_ID  DEBIT_ID  DEBIT_AMOUNT  CREDIT_ID  CREDIT_AMOUNT  AGE
------------------------------------------------------------------
         1     12345         75.00      12346         -75.00   10
         1     12345         25.00      12348         -25.00   15
         1     12347         25.00      12348         -25.00    4
         1     12347         75.00      NULL            NULL  NULL

EDIT: the beauty of the off the shelf system my company has chosen is that payment application is configurable, meaning there is no direct link of a credit to a debit. We apply monies to oldest debt first, which is what I was trying to show in the example. The first payment gets applied completely to the oldest debt. The second payment gets split between the oldest and second oldest debt. This continues on until all credits have been applied.

EDIT 2: sorry this seems to be difficult for me to explain :) Looking at the example data, FT 12346 completely applies to FT 12345, leaving $25 of debt on the account. The next payment would then also apply to this debt, with any remaining amount applied to the next oldest debt. A running total doesn't match exactly what I'm trying to accomplish, because I need to know how old each "slice" of the match is:

EDIT 3: The table above was not clear at all, sorry. I've updated the table.

                 Debits                 Credits
        +-----------------------+-------------------------+
        |     FT 12345          |      FT 12346           |
        |                       |                         |
        |     $100.00           |       $75.00            |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       +-------------------------+
        |                       |      FT 12348           |
        |                       |       $50.00            |
        |                       |                         +----->$25.00 towards 12345
        +-----------------------|.........................|
        |    FT 12347           |                         |
        |                       |                         +----->$25.00 towards 12347
        |      $100.00          +-------------------------+
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        |                       |                         |
        +-----------------------+-------------------------+

We have a solution for this working with a cursor, but across the whole financial transaction table (~50m rows) this is extremely slow. I'm wondering if there's a way to restate the problem in terms of pure tables to speed this up. I found a recipe for genreating a running balance which seems like a start, but I'm not sure where to go from there.

Upvotes: 3

Views: 1836

Answers (1)

Stephen ODonnell
Stephen ODonnell

Reputation: 4466

If an understanding what you want correctly, you can do this with analytics:

create table cred_deb (account_id integer, transaction_date date, amount number);


insert into cred_deb values (1, sysdate - 10, 100.00); -- bill of 100
insert into cred_deb values (1, sysdate - 9, -10.00); -- payment of 10
insert into cred_deb values (1, sysdate - 8, -80.00); -- payment of 80
insert into cred_deb values (1, sysdate - 5, 80.00); -- bill of 80
insert into cred_deb values(1, sysdate - 3, -80.00); -- payment of 80

-- 2nd account
insert into cred_deb values(2, sysdate - 3, 80.00); -- bill of 80
insert into cred_deb values(2, sysdate - 3, -80.00); -- payment of 80



select account_id, 
       transaction_date,
       amount,
       sum(amount) over( partition by account_id order by transaction_date) running_total
from cred_deb
order by account_id, transaction_date;

ACCOUNT_ID             TRANSACTION_DATE          AMOUNT                 RUNNING_TOTAL          
---------------------- ------------------------- ---------------------- ---------------------- 
1                      06-DEC-11                 100                    100                    
1                      07-DEC-11                 -10                    90                     
1                      08-DEC-11                 -80                    10                     
1                      11-DEC-11                 80                     90                     
1                      13-DEC-11                 -80                    10                     
2                      13-DEC-11                 80                     80                     
2                      13-DEC-11                 -80                    0

Looking closer at your example, maybe you want to group all credits for a particular bill along with the bill amount onto one row. If you can give a more clear description of what you what, I think analytics will be able to solve it.

EDIT - added age in days.

select account_id, 
       transaction_date,
       amount,
       sum(amount) over( partition by account_id order by transaction_date) running_total,
       trunc(transaction_date) - max(case 
                                      when amount > 0 then 
                                        trunc(transaction_date)
                                      else
                                        null
                                      end) over (partition by account_id order by transaction_date)  age_in_days
from cred_deb
order by account_id, transaction_date

Upvotes: 1

Related Questions