CODEWITHSUNDEEP
sql-server-2008
zain ularifeen
zain ularifeen

Reputation: 11

How to calculate running balance from the credit and debit column

enter image description here I am Using the following query to calculate running balance.It's working perfectly for me.i need date wise report look like Output 2.

Select  
t2.tentryno,t2.tseqid,t2.glcode,t2.descript,t2.Debit,t2.Credit,t2.entrydate,
(Select SUM(Debit-Credit) From balancesheet As t1 Where 
t1.glcode=t2.glcode  and t1.tseqid  <= t2.tseqid 
)as Amount
 From balancesheet  As t2 where glcode='01-04-0003'
 order by glcode,tseqid,entrydate

for Output 1 and Output 2 plz check image link.

Plz help me

Upvotes: 1

Views: 797

Answers (1)

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

Please try this:

Select
  t.tentryno,t.tseqid,t.glcode,t.descript,t.Debit,t.Credit,t.entrydate, 
  (Select SUM(x.Debit-x.Credit) From balancesheet x 
    Where x.glcode = t.glcode 
    and (
       x.entrydate < t.entrydate 
       or 
       (x.entrydate = t.entrydate and x.tentryno <= t.tentryno))
  ) as Amount 
From balancesheet As t
where t.glcode = '01-04-0003' 
order by t.glcode, t.entrydate, t.tentryno

Since you did not provide schema and data I had to do this in my test system, so perhaps you find some "typos".

Upvotes: 1

Related Questions