Search code examples
sqliteaccounting

SQLite: Running balance with an ending balance


I have an ending balance of $5000. I need to create a running balance, but adjust the first row to show the ending balance then sum the rest, so it will look like a bank statement. Here is what I have for the running balance but how can I adjust row 1 to not show a sum of the first row, but the ending balance instead.

with BalBefore as (
  select *
  from transactions
  where ACCT_NAME = 'Real Solutions'
  ORDER BY DATE DESC
)

select
  DATE,
  amount,
  '$' || printf("%.2f", sum(AMOUNT) over (order by ROW_ID)) as Balance
from BalBefore;

This gives me"

DATE         AMOUNT     BALANCE
9/6/2019     -31.00     $-31.00      <- I need this balance to be replaced with $5000 and have the rest
9/4/2019      15.00     $-16.00         sum as normal.
9/4/2019      15.00     $-1.00
9/3/2019     -16.00     $-17.00

I have read many other questions, but I couldn't find one that I could understand so I thought I would post a simpler question.


Solution

  • The following is not short and sweet, but using the WITH statement and CTEs, I hope that the logic is apparent. Multiple CTEs are defined which refer to each other to make the overall query more readable. Altogether the goal was just to add a beginning balance record that could be :

    /*
    DROP TABLE IF EXISTS data;
    CREATE temp TABLE data (
           id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
           date DATETIME  NOT NULL,
           amount NUMERIC NOT NULL
    );   
    
    INSERT INTO data
    (date, amount)
    VALUES
         ('2019-09-03', -16.00),
         ('2019-09-04',  15.00),
         ('2019-09-04',  15.00),
         ('2019-09-06', -31.00)
    ;
    */
    
    WITH 
         initial_filter AS (
             SELECT id, date, amount
             FROM data
             --WHERE ACCT_NAME = 'Real Solutions'
         ),
         prepared AS (
             SELECT *
             FROM initial_filter
             UNION ALL
             SELECT 
                9223372036854775807 as id, --largest signed integer 
                (SELECT MAX(date) FROM initial_filter) AS FinalDate, 
                -(5000.00) --ending balance (negated for summing algorithm)
         ),
         running AS (
              SELECT
                    id,
                    date,
                    amount,
                    SUM(-amount) OVER 
                        (ORDER BY date DESC, id DESC 
                         RANGE UNBOUNDED PRECEDING 
                         EXCLUDE CURRENT ROW) AS balance
              FROM prepared
              ORDER BY date DESC, id DESC
         )
    SELECT *
    FROM running
    WHERE id != 9223372036854775807
    ORDER BY date DESC, id DESC;
    

    This produces the following

    id  date         amount balance
    4   2019-09-06  -31.00  5000
    3   2019-09-04   15.00  5031
    2   2019-09-04   15.00  5016
    1   2019-09-03  -16.00  5001
    

    UPDATE: The first query was not producing the correct balances. The beginning balance row and the windowing function (i.e. OVER clause) were updated to accurately sum over the correct amounts.
    Note: The balance on each row is determined completely from the previous rows, not from the current row's amount, because this works backward from an ending balance, not forward from the previous row balance.