calculate columns on where condition

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

varsha dodiya11y ago
@jignesh trivedi , this will not work out to get desired result
Jignesh Trivedi11y ago
Hi,
you can add date filter in where condition
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
WHERE P_Date between '2015-01-28' and '2015-01-28'
GROUP BY P_sname
ORDER BY P_sname DESC

hope this will help you.