Postgres problem

Question

Say I have the following data in my table;

tran_date    withdraw     deposit
25/11/2010          0         500
2/12/2010         100           0
15/12/2010          0         300
18/12/2010          0         200
25/12/2010        200           0

Suppose I want to get the following for date range between 1/12/2010 and 31/12/2010.

tran_date    withdraw    deposit    balance     days_since_last_tran
1/12/2010           0          0        500                        0
2/12/2010         100          0        400                        1
15/12/2010          0        300        700                       13
18/12/2010          0        200        900                        3
25/12/2010        200          0        700                        7
31/12/2010          0          0        700                        6

Is this doable in PostgreSQL 8.4?

OMG Ponies · Accepted Answer · 2010-12-20 05:38:12Z

3

Use:

SELECT t.tran_date,
       t.withdraw,
       t.deposit,
       (SELECT SUM(y.deposit) - SUM(y.withdrawl)
          FROM YOUR_TABLE y
         WHERE y.tran_date <= t.tran_date) AS balance,
       t.tran_date - COALESCE(LAG(t.tran_date) OVER(ORDER BY t.tran_date), 
                              t.tran_date) AS days_since_last
  FROM YOUR_TABLE t

8.4+ is nice, providing access to analytic/windowing functions like LAG.

edited Dec 20, 2010 at 5:38

answered Dec 20, 2010 at 5:21

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rabin Over a year ago

Giving me an error :: 'ERROR: COALESCE types date and integer cannot be matched'

Rabin Over a year ago

@OMG Ponies :: I still don't get the edges if I put on a date range.

OMG Ponies Over a year ago

@Rabin: "edges"? I'm not telepathic -- if you want to apply a date range, state such in your question.

Rabin Over a year ago

@OMG Ponies :: But I have said in my question that I want to apply date range and want to see the first and the last day of the date range though there may not be any transaction on those days.

Jean Hominal Over a year ago

@OMG Ponies: The 1st and last line in the proposed result do not correspond to a table entry.

Collectives™ on Stack Overflow

Postgres problem

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related