Solved

How Can I add Running Balance Column

Posted on 2012-03-12
1
461 Views
Last Modified: 2012-03-12
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
Comment
Question by:Mehram
1 Comment
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37709207
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now