Solved

Sql Server Running Total

Posted on 2012-04-13
11
254 Views
Last Modified: 2012-08-14
How can make a query with a running total.  I have the following fields in table


Customer Nº    Date              Doc             Deb   Cred   Total (Deb-Cred)  
    1                2012/01/01     1000             40       0                 40
    1                2012/0112      2580             30      10                60
    1                2012/02/15     2620             50        0              110  

I just need to do a query for a customer at a time order by date.
0
Comment
Question by:rflorencio
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37843608
One method would be something like:

SELECT T1.CustomerNo, T1.[Date], T1.Doc, T1.Deb, T1.Cred, Sum(T2.Deb + T2.Cred) as Balance
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T1.CustomerNo = T2.CustomerNo
AND T1.[Date] >= T2.[Date]
GROUP BY T1.CustomerNo, T1.[Date], T1.Doc, T1.Deb, T1.Cred

But if you have multiple entries for a particular customer on a particular day, and your date field does not include a time component, then you the Balance column would not be accurate for those records.
0
 

Expert Comment

by:BabarZaman
ID: 37843739
You need to be more specific.
Running total goes to number of counts.

For now I am assuming based on above mentioned table that you wanted to Total(Deb-Cred)

select Sum(deb-cred) as 'RunningTotal' from tablename1 inner join Tablename2
on tablename1.col1=tablename2.col2 where tablename1.date>tablename2.date


If you clear your requirement, I could help you more.
0
 

Author Comment

by:rflorencio
ID: 37843864
All the following columns are in the same table,

Customer Nº    Date            Doc          Deb   Cred  balance
    1                2012/01/01   1000          40       0         40                
    1                2012/0112    2580          30      10        60  (deb-cred + previous row balance)
    1                2012/02/15   2620          50        0      110  (deb-cred + previous row balance)

I need the accumulated line by line.
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 41

Expert Comment

by:ralmada
ID: 37843968
try

select       *,
      (select sum(deb - cred) from yourtable where customerid = a.customerid and [date] <= a.[date]) as total
from yourtable a
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 37844156
The way your requirement reads, you want an Excel like functionality based on the rows.

Because SQL uses SET based retrieval, you won't get a row by row balance without using something like Excel.  SQL will not do that type of calculations based on the rows or row above it.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37844189
dbaduck,

Of course it will.  Take a look at the queries suggested by me or ralmada.  Both of those queries would produce the requested results, not sure which would be quicker, so I would personally test both, if it were me.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 37844209
This is only because the Dates are not duplicated. But once you have a date that is duplicated and you have 2 transactions, you will get erroneous rows.

But you are right, when the conditions are ideal, you get what you expect, but once things step outside of ideal, you don't get what you want.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 37844232
>>... when the conditions are ideal, you get what you expect, but once things step outside of ideal, you don't get what you want.<<

Well, let's make things ideal then:

;with cte as (
	select *, row_number() over (partition by customerid order by [date]) rn
	from yourtable
)
select       *,
      (select sum(deb - cred) from cte where customerid = a.customerid and rn <= a.rn) as total
from cte a
order by customerid, [date]

Open in new window

0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 37844256
Very nice.  Conditions would now be ideal.  

My only comment was relating to the engine itself, but your query shows that with some TSQL magic, you can indeed obtain what you want.

From a DBA perspective, I tend to think of performance and on a very large dataset this would not perform extremely well and would perform like a cursor, which is exactly what is required to get these results, but it would indeed work well for the solution to the question.
0
 

Author Comment

by:rflorencio
ID: 37875198
ralmada, for my needs is perfect. Only one more thing it´s possible hide "rn" column
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 37875428
Yes,

Just drop the * and declare columns explicitly there omitting the rn one.

;with cte as (
      select *, row_number() over (partition by customerid order by [date]) rn
      from yourtable
)
select       customerid, [date], doc, deb, cred,
      (select sum(deb - cred) from cte where customerid = a.customerid and rn <= a.rn) as total
from cte a
order by customerid, [date]
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

617 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