• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

How Can I add Running Balance Column

I am using below mentioned query to get result from database. Can some one help me to add running balance in this query.

Thanks in advance.

-----

Select * from
(Select Disc='PON-'+Isnull(PON,'')+' Vendor Invoice # '+ BN + ' Receipt Date '+ Convert(Varchar(12),Date),
       a.Date,b.VendorName,SNo=Row_Number() Over (Partition by BN Order by bn,a.Transno), c.FullItem,Qty,Rate,Debit=0, Credit=Amt
from mtPurchased a
join mtVendor b on a.VendorCode=b.Transno
join mtIPUOM c on a.IC=c.Transno
Where b.VendorName='Arman Autos' and a.del=0
--Order by bn,a.Transno
Union All
Select Disc='Paid Through ' + c.OS + ' ' + 'Paid To ' + b.VendorName + ' On ' +Convert(Varchar(12),Pdate) + ' Ref No. ' +a.ACRefNo + 'Amount Rs. ' + Convert(Varchar(10),sum(amt)),
            PDate,b.VendorName, Sno=Row_Number() Over (Partition by ACRefNo Order by Pdate,ACRefNo), AcRefNo, Qty=0, Rate=0, Debit=Sum(Amt), Credit=0
from mtPayment a
join mtVendor b on a.VendorCode=b.Transno
join mtOppStaff c on a.OS=c.Transno
Where b.VendorName='Arman Autos'
group by c.OS, b.VendorName,a.PDate, a.ACRefNo
--Order by Pdate,ACRefNo
)x
order by date
0
Mehram
Asked:
Mehram
1 Solution
 
jogosCommented:
How to running total see options  at
http://www.sqlteam.com/article/calculating-running-totals

Put option 1 in a CTE-sollution http://msdn.microsoft.com/en-us/library/ms175972.aspx
Don't foget to attend the TODO's mentioned.

One thing I didn't catch is how your running total is for outcome of the union or for each part of the union -> handle in the subquery cte1 vs cte2

;WITH CTE () as (  , , )   -- TODO columnlist
(Select Disc='PON-'+Isnull(PON,'')+' Vendor Invoice # '+ BN + ' Receipt Date '+ Convert(Varchar(12),Date),
       a.Date,b.VendorName,SNo=Row_Number() Over (Partition by BN Order by bn,a.Transno), c.FullItem,Qty,Rate,Debit=0, Credit=Amt 
from mtPurchased a
join mtVendor b on a.VendorCode=b.Transno
join mtIPUOM c on a.IC=c.Transno
Where b.VendorName='Arman Autos' and a.del=0
--Order by bn,a.Transno
Union All
Select Disc='Paid Through ' + c.OS + ' ' + 'Paid To ' + b.VendorName + ' On ' +Convert(Varchar(12),Pdate) + ' Ref No. ' +a.ACRefNo + 'Amount Rs. ' + Convert(Varchar(10),sum(amt)),
            PDate,b.VendorName, Sno=Row_Number() Over (Partition by ACRefNo Order by Pdate,ACRefNo), AcRefNo, Qty=0, Rate=0, Debit=Sum(Amt), Credit=0
from mtPayment a
join mtVendor b on a.VendorCode=b.Transno
join mtOppStaff c on a.OS=c.Transno
Where b.VendorName='Arman Autos' 
group by c.OS, b.VendorName,a.PDate, a.ACRefNo
--Order by Pdate,ACRefNo
) 
select ct1.*
 , (select sum(cte2. )   -- TODO how is your balance-constructed
     from cte as ct2
     where ct2. = ct1.   --- TODO  the whole join-condition 
     and ct2.date <= ct1.date
     and ct2.SNo <= ct1.SNo
   ) as runningBalance
from CTE as cte1
order by date,SNo

Open in new window

0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now