Opening and Balance amount query for different account in postgresql
up vote
-1
down vote
favorite
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
sql postgresql
asked Nov 12 at 6:27
Zakir Hossain
3610
|
show 1 more comment
up vote
-1
down vote
favorite
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
sql postgresql
asked Nov 12 at 6:27
Zakir Hossain
3610
5
How much are you offering to pay for someone to write this query for you?
– Caius Jard
Nov 12 at 6:47
and there is an up vote
– guradio
Nov 12 at 6:47
Hi i see your profile.. And you never accepted answer and rarely put up your query into your question.. Please.. Do a favor for them like accepted the answer.. And we are here not do free service code for you..
– dwir182
Nov 12 at 6:52
#Caius Jard #guradil #dwir182, sorry dear i am a learner and unable to pay...
– Zakir Hossain
Nov 12 at 8:57
The Debit and Credit columns seem to be reversed in your example input.
– Mahesh H Viraktamath
Nov 12 at 9:13
|
show 1 more comment
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
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
sql postgresql
asked Nov 12 at 6:27
Zakir Hossain
3610
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
sql postgresql
sql postgresql
asked Nov 12 at 6:27
Zakir Hossain
3610
asked Nov 12 at 6:27
Zakir Hossain
3610
edited Nov 15 at 10:04
asked Nov 12 at 6:27
Zakir Hossain
3610
asked Nov 12 at 6:27
Zakir Hossain
3610
asked Nov 12 at 6:27
Zakir Hossain
3610
3610
5
How much are you offering to pay for someone to write this query for you?
– Caius Jard
Nov 12 at 6:47
and there is an up vote
– guradio
Nov 12 at 6:47
Hi i see your profile.. And you never accepted answer and rarely put up your query into your question.. Please.. Do a favor for them like accepted the answer.. And we are here not do free service code for you..
– dwir182
Nov 12 at 6:52
#Caius Jard #guradil #dwir182, sorry dear i am a learner and unable to pay...
– Zakir Hossain
Nov 12 at 8:57
The Debit and Credit columns seem to be reversed in your example input.
– Mahesh H Viraktamath
Nov 12 at 9:13
|
show 1 more comment
5
How much are you offering to pay for someone to write this query for you?
– Caius Jard
Nov 12 at 6:47
and there is an up vote
– guradio
Nov 12 at 6:47
Hi i see your profile.. And you never accepted answer and rarely put up your query into your question.. Please.. Do a favor for them like accepted the answer.. And we are here not do free service code for you..
– dwir182
Nov 12 at 6:52
#Caius Jard #guradil #dwir182, sorry dear i am a learner and unable to pay...
– Zakir Hossain
Nov 12 at 8:57
The Debit and Credit columns seem to be reversed in your example input.
– Mahesh H Viraktamath
Nov 12 at 9:13
5
5
How much are you offering to pay for someone to write this query for you?
– Caius Jard
Nov 12 at 6:47
How much are you offering to pay for someone to write this query for you?
– Caius Jard
Nov 12 at 6:47
and there is an up vote
– guradio
Nov 12 at 6:47
and there is an up vote
– guradio
Nov 12 at 6:47
Hi i see your profile.. And you never accepted answer and rarely put up your query into your question.. Please.. Do a favor for them like accepted the answer.. And we are here not do free service code for you..
– dwir182
Nov 12 at 6:52
Hi i see your profile.. And you never accepted answer and rarely put up your query into your question.. Please.. Do a favor for them like accepted the answer.. And we are here not do free service code for you..
– dwir182
Nov 12 at 6:52
#Caius Jard #guradil #dwir182, sorry dear i am a learner and unable to pay...
– Zakir Hossain
Nov 12 at 8:57
#Caius Jard #guradil #dwir182, sorry dear i am a learner and unable to pay...
– Zakir Hossain
Nov 12 at 8:57
The Debit and Credit columns seem to be reversed in your example input.
– Mahesh H Viraktamath
Nov 12 at 9:13
The Debit and Credit columns seem to be reversed in your example input.
– Mahesh H Viraktamath
Nov 12 at 9:13
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
***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
answered Nov 15 at 10:15
Zakir Hossain
3610
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
***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
answered Nov 15 at 10:15
Zakir Hossain
3610
add a comment |
up vote
0
down vote
accepted
***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
answered Nov 15 at 10:15
Zakir Hossain
3610
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
***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
answered Nov 15 at 10:15
Zakir Hossain
3610
***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
answered Nov 15 at 10:15
Zakir Hossain
3610
answered Nov 15 at 10:15
Zakir Hossain
3610
answered Nov 15 at 10:15
Zakir Hossain
3610
answered Nov 15 at 10:15
Zakir Hossain
3610
3610
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53256895%2fopening-and-balance-amount-query-for-different-account-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
5
How much are you offering to pay for someone to write this query for you?
– Caius Jard
Nov 12 at 6:47
and there is an up vote
– guradio
Nov 12 at 6:47
Hi i see your profile.. And you never accepted answer and rarely put up your query into your question.. Please.. Do a favor for them like accepted the answer.. And we are here not do free service code for you..
– dwir182
Nov 12 at 6:52
#Caius Jard #guradil #dwir182, sorry dear i am a learner and unable to pay...
– Zakir Hossain
Nov 12 at 8:57
The Debit and Credit columns seem to be reversed in your example input.
– Mahesh H Viraktamath
Nov 12 at 9:13