Calculation of the daily minimum balance

Question

Let's say I have a table with a transaction with transactions, deposits, withdrawals. There may or may not be a transaction per day, but it may have several transactions for each day. So, I need to give a range of dates, say, from December 1, 2010 to December 31, 2010, I need to find out the minimum balance for each day. Suppose there are transactions before December 1, 2010. Is there anyone who can give me an idea about this?

Thanks.

Update with an example

tran_date withdraw deposit 2010-11-23 0.00 50.00 2010-12-10 0.00 50.00 2010-12-10 0.00 200.00 2010-12-12 100.00 0.00 2010-12-20 0.00 50.00 2010-12-20 70.00 0.00 2010-12-20 0.00 50.00 2010-12-20 0.00 50.00 2010-12-24 150.00 0.00 

In the above example, the minimum daily balance from December 1 to December 10 would be 50 . Ten deposits totaling 70 , but the minimum balance on this day will be 50 (carried over from the previous day).

Now consider a few transactions.

Inverted December 20, 200 . The first contribution makes it 250 , the second makes it 180 , the third makes it 230 , and the last transaction makes it 280 . Thus, the minimum balance for this day would be 180 after withdrawing 70 in the second transaction of the day. Is it possible to generate this with a PostgreSQL 8.4 query or use a different approach?

Answer 1

Edit2
This is a complete example, including the (minimum) balance from the previous day (as far as I can tell with such a small data set). It should work on 8.4.

I reorganized the views to use CTE (generic table expression) to make it (hopefully) more readable:

  WITH days AS (
    - generate a liste of possible dates spanning 
    - the whole interval of the transactions
    SELECT min (tran_date) + generate_series (0, max (tran_date) - min (tran_date)) AS some_date
    FROM transaction
 ),
 total_balance AS (
   - Calculate the running totals for all transactions
   SELECT tran_id,
          days.some_date as tran_date, 
          deposit 
          withdrawal
          sum (deposit - withdrawal) 
              OVER (ORDER BY some_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as balance
   FROM days
    LEFT JOIN transaction t ON t.tran_date = days.some_date 
 ),
 min_balance AS (
   - calculate the minimum balance for each day 
   - (the smalles balance will have a '1' in the column balance_rank)
   SELECT tran_id, 
          tran_date,
          rank () OVER (PARTITION BY tran_date ORDER BY balance) as balance_rank,
          balance
   FROM total_balance
 )
 - Now get everything, including the balance for the previous day
 SELECT tran_id,
        tran_date,
        balance
        lag (balance) over (order by tran_date) as previous_balance
 FROM min_balance
 WHERE balance_rank = 1;

Answer 2

Ignore everything in this other answer. This guy Malvolio is a fool and an idiot. Try instead:

 SELECT MIN(balance), transaction_date FROM ( SELECT a.transaction_date, IFNULL(sum(b.deposit) - sum(b.withdrawal), 0) balance FROM transaction a LEFT JOIN transaction b ON a.seqno > b.seqno GROUP ON a.seqno UNION SELECT a.transaction_date, IFNULL(sum(b.deposit) - sum(b.withdrawal), 0) balance FROM transaction a LEFT JOIN transaction b ON a.seqno >= b.seqno GROUP ON a.seqno ) x GROUP BY transaction_date; 

I was just about to fall asleep when this happened to me. IFNULL may be specific to MySQL, but you can find the equivalent of Postgres.

Answer 3

I assume with the minimum balance that you are talking about, what do you have less about, at the beginning or at the end of the day?

I suppose you do something like this every day:

Balance the day before:

 SELECT (SUM(deposit) - SUM(withdrawal)) WHERE date < [date you're after] 

(I don't know how date comparison in PostgreSQL would be done

Then:

 SELECT (SUM(deposit) - SUM(withdrawal)) WHERE date = [date you're after] 

Then that is more.

If this is not what you had in mind, we need more information.

Answer 4

First, I'm going to assume that transactions are sequentially numbered. Just by definition, transactions should be properly ordered (because a deposit of $ 50, followed by a withdrawal of $ 50 on the same day, may give a completely different answer from the same steps in a different order), and their numbering sequentially simplifies other things. Then we must perform some procedural operations:

 CREATE TABLE running_total (seqno INT, transaction_date DATE, before NUMBER(10,20), after NUMBER(10,20); SET tot=0; FOR transaction IN SELECT * FROM transaction ORDER BY seqno ASC LOOP SET oldtot = tot; SET tot = tot = transaction.deposit - transaction.withdrawal; EXECUTE 'INSERT INTO running_total (seqno, transaction_date, before, after) VALUES (' || transaction.seqno || ', ' || transaction.transaction_date || ',' || oldtot || ',' || tot || ')'; END LOOP; 

(Forgive any typos - I don't have PostGres). Now we have a table with all the leftovers in it, we just need to dig it out.

 SELECT MIN(balance), transaction_date FROM ( SELECT before as balance, transaction_date FROM running_total UNION SELECT after as balance, transaction_date FROM running_total) x GROUP BY transaction_date; 

I can't check it out here, but it should work.

Answer 5

Assuming you make transactions during the day, I adopted the following scheme:

 CREATE TABLE transaction ( tran_date date, num int, withdraw numeric, deposit numeric ); INSERT INTO transaction VALUES ('2010-11-23', 1, 0.00, 50.00), ('2010-12-10', 1, 0.00, 50.00), ('2010-12-10', 2, 0.00, 200.00), ('2010-12-12', 1, 100.00, 0.00), ('2010-12-20', 1, 0.00, 50.00), ('2010-12-20', 2, 70.00, 0.00), ('2010-12-20', 3, 0.00, 50.00), ('2010-12-20', 4, 0.00, 50.00), ('2010-12-24', 1, 150.00, 0.00); 

Then the following request appears, which will give you an answer:

 WITH dates (tran_date) AS (SELECT date '2010-12-01' + generate_series(0, 30)), transactions AS (SELECT tran_date, num, coalesce(withdraw, 0) AS withdraw, coalesce(deposit, 0) AS deposit FROM dates FULL OUTER JOIN transaction USING (tran_date)), running_totals AS (SELECT tran_date, sum(deposit - withdraw) OVER (ORDER BY tran_date, num ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS lagging_total, sum(deposit - withdraw) OVER (ORDER BY tran_date, num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_total FROM transactions) SELECT tran_date, min(least(lagging_total, current_total)) FROM running_totals GROUP BY tran_date HAVING tran_date IN (SELECT tran_date FROM dates) ORDER BY tran_date; 

Please note that for this you need PostgreSQL 9.0, because the 1 PRECEDING not supported in earlier versions. If you cannot update, you will probably need some kind of procedural solution, like the other answers.

In any case, I recommend writing unit tests for this .; -)

Answer 6

Why don't you add a column to the database that tracks the current balance (calculated for every deposit / withdrawal). Thus, this is just a case of returning the minimum for this column to the date range you are interested in.

Answer 7

Thank you all for your help. To solve this problem, I used the following. I don't know how efficient the code is.

 select dt::date, coalesce(case when balance<=coAmt then balance else coAmt end, (select sum(coalesce(deposit, 0.00))-sum(coalesce(withdraw, 0.00)) from where tran_date<=dt::date and acc_no='3'), 0.00) amt from ( select tran_date, min(balance) balance, coalesce((select sum(coalesce(deposit, 0.00) - coalesce(withdraw, 0.00)) from transaction where tran_date<t.tran_date and acc_no=t.acc_no), 0.00) coAmt from ( select tran_id, acc_no, tran_date, deposit, withdraw, sum(deposite - withdraw) over (order by tran_id) balance from transaction sv group by tran_id, acc_no, tran_date, deposite, withdraw) t where acc_no='3' group by tran_date, acc_no order by tran_date ) t1 right join generate_series('2010-12-01', '2010-12-31', interval '1 day') as dt on dt=tran_date group by dt, tran_date, balance, coAmt order by dt 

Thanks again for your help.