Pfthb

I'm trying to set opening and Balance column in postgresql query where i input specific date range and first opening data should be upto starting date and Balance should be (opening data + Debit - Credit) in each date wise row.

*** Here is my sample database name "opening_and_closing"

date1 acc_no debit credit
01/01/2017 a 500 0 
02/01/2017 a 0 400 
03/01/2017 a 100 0 
04/01/2017 a 800 0 
05/01/2017 a 0 700 
06/01/2017 a 800 0 
01/01/2017 b 500 0 
02/01/2017 b 0 400 
03/01/2017 b 100 0 
04/01/2017 b 800 0 
05/01/2017 b 0 700 
06/01/2017 b 800 0 

* My expected query in postgresql
* date range is 03/01/2017 to 06/01/2017

 opening : 100
 date1 acc_no debit credit balance
 03/01/2017 a 100 0 200
 04/01/2017 a 800 0 1000
 05/01/2017 a 0 700 300
 06/01/2017 a 800 0 1100
 opening : 100
 date1 acc_no debit credit balance 
 03/01/2017 b 100 0 200
 04/01/2017 b 800 0 1000
 05/01/2017 b 0 700 300
 06/01/2017 b 800 0 1100

***Create a table name "opening_and_closing" in your database

create table opening_and_closing (date1 date, acc_no varchar(250), debit numeric,credit numeric);

***Insert Values in your "opening_and_closing" table

insert into opening_and_closing
values ('2017-01-01','a',500,0),
('2017-01-02','a',0,400),
('2017-01-03','a',100,0),
('2017-01-04','a',800,0),
('2017-01-05','a',0,700),
('2017-01-06','a',800,0),
('2017-01-01','b',500,0),
('2017-01-02','b',0,400),
('2017-01-03','b',100,0),
('2017-01-04','b',800,0),
('2017-01-05','b',0,700),
('2017-01-06','b',800,0);

***Now Execute the following query

select date1,acc_no,opening,debit,credit,
 (opening+sum(debit-credit) OVER (partition by acc_no order by date1)) as balance
from

 (select date1,acc_no,debit,credit,
 (select sum(debit)-sum(credit) from opening_and_closing where date1 < '2017-01-03' and acc_no ='a') as opening

 from opening_and_closing
 where date1 between '2017-01-03' and '2017-01-06'
 and acc_no ='a'
 group by 1,2,3,4
 order by date1)x