Mehram
asked on
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=Ro w_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(am t)),
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
Thanks in advance.
-----
Select * from
(Select Disc='PON-'+Isnull(PON,'')
a.Date,b.VendorName,SNo=Ro
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.