Sql Server Running Total

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.
rflorencioAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ralmadaConnect With a Mentor Commented:
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
 
Dale FyeCommented:
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
 
BabarZamanCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
rflorencioAuthor Commented:
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
 
ralmadaCommented:
try

select       *,
      (select sum(deb - cred) from yourtable where customerid = a.customerid and [date] <= a.[date]) as total
from yourtable a
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
Dale FyeCommented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
ralmadaCommented:
>>... 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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
rflorencioAuthor Commented:
ralmada, for my needs is perfect. Only one more thing it´s possible hide "rn" column
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.