Solved

Sql Server Running Total

Posted on 2012-04-13
11
247 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
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
separate column 24 20
SQL JOIN 6 35
SQL Server issue connecting to named instance 6 42
t-sql splitting name column 5 23
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

757 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

14 Experts available now in Live!

Get 1:1 Help Now