varsha dodiya

varsha dodiya

  • NA
  • 407
  • 73.8k

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_DebitRemaining 
NULL varsha opening 20000 0 2000
  2014-01-25 00:00:00.000 varsha purchase500500
 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 varshapurchase 350350 

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_DebitCLossing
NULL varsha 2000850 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 Debitclosing balance
NULL varsha 2500350 0 2850
2015-01-28 00:00:00.000 nipun 1000 200 0 1200

please help  

Answers (2)