TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
varsha dodiya
NA
407
75.4k
calculate columns on where condition
Feb 13 2015 5:09 AM
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
Reply
Answers (
2
)
Like Query..
database