Calculating the daily minimum balance

Let's say I have a table named transaction with transaction_date, deposit, withdrawal fields. There may or may not be transaction on a day but can have multiple transactions for each day. So, what I need to do is given a date range, say December 1, 2010 to December 31, 2010, I need to figure out the minimum balance on each day. Let's assume there are transactions before December 1, 2010 as well. Is there anyone who can give me an idea on this one?

Thank you.

Update With 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 above example, the minimum daily balance from Dec 1 to Dec 10 would be 50. On Dec 10 there are two deposits totaling 70 but the minimum bala开发者_如何学Gonce on that day would be 50 (carried over from previous day).

Now lets look at multiple transactions.

The carried over on Dec 20 is 200. The first deposit makes it 250, the second one makes it 180, the third one makes it 230 and the last transaction makes it 280. So, the minimum balance for that day would be 180 after withdrawing 70 on the second transaction of the day. Is it possible to generate this using a query on PostgreSQL 8.4 or should I use another approach?

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

I refactored the derived tables to use CTE (common table expression) to make it (hopefully) a bit 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;

Ignore everything in that other answer. That guy Malvolio is an blowhard and an idiot. Try this 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 occurred to me. The IFNULL may be a MySQL specific thing, but you can find a Postgres equivalent.

I'm assuming by minimum balance that you're talking about which you have less at, the start or the end of the day?

I suppose for each day you'd do something like this:

Balance from day before:

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

(not sure how date comparison would be done in PostgreSQL

Then:

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

Then whichever is greater.

If that's not what you meant, we need more info.

First, I'm going to assume that the transactions are sequentially numbered. Just by definition, the transactions have to be properly ordered (because a $50 deposit followed by a $50 withdrawal on the same day would produce a very different answer from the same steps in a different order) and numbering them sequentially makes other things much easier. Then we have to do some procedural handwaving:

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 handy). Now we have a table with all the balances in it, we just have 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 test this here, but it should work.

Assuming you number your transactions within a day, I took the following schema:

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 query appears to give you your 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;    

Note, however, that you need PostgreSQL 9.0 for that because the 1 PRECEDING clause is not supported in earlier versions. If you can't upgrade, you will probably need some kind of procedural solution like the other answers suggest.

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

Why don't you add a column to the database that tracks the current balance (calculated at each deposit/withdrawal). That way it would simply a case of returning the minimum for that column within the date range you're interested in.

Thank you everyone for the help. I've used the following to solve this. I don't know how efficient the code is though.

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

Again, thanks for all your help.