get running total of transactions by one date and then determine

get running total of transactions by one date and then determine if they are current by another date

hanks in advance for any help.

I am trying to generate a running total of transactions by the transaction date. Then for each total, seeing if the transactions that make up that total are passed due

for example i'm trying to get the "current?" field (it doesn't have to be the text below it can be a date or amount):

very odd situation i know so let me know if any additional data would be helpful

i tried using the analytics functions to get the running total for transactions (that is working)

but i haven't been able to figure out how to basically take the running total and go back up the sort to see what the max (most recent) due date would be for that amount of money.

I just need to know when the account is current if that helps. hoping to get a sql query or pointed in the right direction to what that query should look like.

adding a copy of one of the queries i tried i was trying to do something around the idea of... if i group by due dt and the running total is greater than the total owed for that particular due date... it must not be current

    select   transacton_date, due_dt,  
 trans_amt, 
sum(trans_amt) over (order by due_Dt) as running_total, 
sum(trans_amt) over (order by transacton_date rows unbounded preceding ) as run_tot_by_freeze
,sum(trans_amt) over (partition by due_dt order by due_Dt) as tot_for_the_due
from (
  select  transacton_date,    sum(trans_amt) trans_amt, due_dt
  from ( 
  select  
                       transacton_date, 
                        fin.trans_amt,
                      
                        case 
                        when due_dt is null then transacton_date else due_dt end as due_dt
   FROM
                       act  act inner join act_type st on st.act_type_cd = act.act_type_cd
                       inner join fin fin on fin.act_id = act.act_id AND fin.transacton_date IS NOT NULL
                       left outer join bill  bill on bill.bill_id = fin.bill_id 
                    WHERE
                        acct_id = '12340000' 
                       
                        )
                        group by 
                transacton_date,
                ft_type_flg,
                due_dt
                 HAVING  SUM(transacton_date) != 0
                   order by transacton_date
         )
         order by transacton_date

Answers

It seems like you're on the right track with using window functions for calculating the running total. To determine whether an account is current based on the running total and due dates, you can use a combination of window functions and conditional logic.

Here's a breakdown of the steps you can take:

1. Calculate the running total of transactions sorted by transaction date.
2. Determine the total amount owed for each due date.
3. For each running total, find the maximum due date for amounts up to that point.
4. Compare the running total to the total amount owed for the corresponding due date to determine if the account is current.

Here's how you can implement this logic in SQL:

WITH RunningTotal AS (
    SELECT
        trans_date,
        due_date,
        transaction_amt,
        SUM(transaction_amt) OVER (ORDER BY trans_date) AS running_total
    FROM
        your_table
),
TotalOwed AS (
    SELECT
        due_date,
        SUM(transaction_amt) AS total_amount_due
    FROM
        your_table
    GROUP BY
        due_date
),
MaxDueDate AS (
    SELECT
        rt.trans_date,
        rt.due_date,
        rt.running_total,
        MAX(to.due_date) AS max_due_date
    FROM
        RunningTotal rt
    JOIN
        TotalOwed to ON rt.due_date = to.due_date
    WHERE
        rt.running_total <= to.total_amount_due
    GROUP BY
        rt.trans_date,
        rt.due_date,
        rt.running_total
)
SELECT
    rt.trans_date,
    rt.due_date,
    rt.transaction_amt,
    rt.running_total,
    CASE
        WHEN rt.running_total >= to.total_amount_due THEN 'No - Account is not current'
        ELSE 'Yes - Account is current'
    END AS current_status
FROM
    RunningTotal rt
JOIN
    TotalOwed to ON rt.due_date = to.due_date
LEFT JOIN
    MaxDueDate mdd ON rt.trans_date = mdd.trans_date
                   AND rt.due_date = mdd.due_date
                   AND rt.running_total = mdd.running_total;

In this query:

• RunningTotal: Calculates the running total of transactions.
• TotalOwed: Calculates the total amount owed for each due date.
• MaxDueDate: Determines the maximum due date for amounts up to each running total.
• The final query joins these CTEs and compares the running total to the total amount owed for the corresponding due date to determine if the account is current.

Adjust the table names and column names according to your actual schema. This query assumes that your_table contains the transaction data with columns trans_date, due_date, and transaction_amt.