Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sql Server Running Total

Posted on 2012-04-13
11
Medium Priority
?
255 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 25

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 25

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 25

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 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

722 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