Tech
Forums
Jobs
Books
Events
Live
Interviews
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
2
Reply
calculate columns on where condition
varsha dodiya
Feb 13 2015 5:09 AM
804
Reply
hie frens,
I have a table ACNT which is like this
P_date
P_Supplier
P_parti
P_Opening _bal
P_Credit
P_Debit
Remaining
NULL
varsha
opening
2000
0
0
2000
2014-01-25 00:00:00.000
varsha
purchase
0
500
0
500
2015-01-28 00:00:00.000
nipun
opening
1000
0
0
1000
2015-01-28 00:00:00.000
nipun
purchase
0
200
0
200
2016-01-25 00:00:00.000
varsha
purchase
0
350
0
350
now i fire this query to get sum or all columns
SELECT P_sname, SUM(P_opnbal) AS opneningbal, SUM(P_credit) AS credit, SUM(P_debit) AS debit, SUM(P_opnbal) + SUM(P_credit) - SUM(P_debit) AS closingbal
FROM ACNT
GROUP BY P_sname
ORDER BY P_sname DESC
and i get this result
P_date
P_Supplier
Opening _bal
P_Credit
P_Debit
CLossing
NULL
varsha
2000
850
0
2850
2015-01-28 00:00:00.000
nipun
1000
200
0
1200
but i Want this columns to be calculated in between particular time range.
for example,
if i want data in between 2015-01-28 and 2016-01-28.
then all the data present in table of date 2014-01-28 should be shown as Opening balance.
means
[see the yellow row in table]
that row has date 2014-01-28 then its P_credit value should be plus with its P_opening_bal value i.i 2000 . so it should show 2500 as opening balance as result.
desired result should be like
P_date
P_Supplier
Opening _bal
credit
Debit
closing balance
NULL
varsha
2500
350
0
2850
2015-01-28 00:00:00.000
nipun
1000
200
0
1200
please help
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
2
)
Next Recommended Forum
Like Query..
database