2
Answers

calculate columns on where condition

Photo of varsha dodiya

varsha dodiya
10y
1.3k
1
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 purchase0 5000 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 varshapurchase 0 3500 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_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)

0
Photo of varsha dodiya
NA 407 76.8k 10y
@jignesh trivedi , this will not work out to get desired result
-1
Photo of Jignesh Trivedi
NA 62.3k 46.3m 10y

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.
Next Recommended Forum