Sql Server Running Total

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.

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.

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.

try

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

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.

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.

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.

>>... 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]

Select allOpen in new window

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.

ralmada, for my needs is perfect. Only one more thing it´s possible hide "rn" column