CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
mamcx
mamcx

Reputation: 16186

Window function for rest the first value from Column1 against values of Column2

I need to get the balance for a data that look like this:

The base tables:

DebtsHeader
Zone, Id, Date  , TotalDebt
1,     1, 10-Jan,       100
2,     2, 11-Jan,       200
2,     3, 11-Jan,       300

DebtsPayment
Debt, Date  , Pay
1,    10-Jan, 10
2,    11-Jan, 20
2,    12-Jan, 30
3,    11-Jan, 30

I have a view that join both and get this:

Zone, Debt, Date  , TotalDebt,  Pay
1,       1, 10-Jan,       100,   10
2,       3, 10-Jan,       500,   30
2,       2, 11-Jan,       500,   20
2,       2, 12-Jan,       500,   30

Now, I need to get the balance by zone & date, ej:

Zone, Date  , Balance
1,    10-Jan,      90
2,    10-Jan,     470
2,    11-Jan,     450
2,    12-Jan,     420

I can do this with imperative code, but wonder if window functions could make this work.

Upvotes: 0

Views: 27

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use the window function sum here.

select zone, date, totaldebt-sum(pay) over(partition by zone order by date)
from your_view

Upvotes: 1

Related Questions