1

This is my SQL Server 2008 R2 table:

ID  Amount  date_time
001 9.00    01/01/2015 01:01:00
001 20.00   01/02/2015 01:02:00
001 20.00   01/02/2015 01:03:00 
002 20.00   01/01/2015 01:01:00
002 20.00   01/01/2015 01:02:00
003 20.00   01/02/2015 00:00:00
003 10.00   01/03/2015 00:00:00
003 5.00    01/04/2015 00:00:00

I need it to be like this with a running total (ordered by Id, and date/time):

ID  Amount  running amount    date/time
001 9.00    9.00              1/1/2015 01:01:00
001 20.00   29.00             1/2/2015 01:02:00
001 20.00   49.00             1/2/2015 01:03:00
002 20.00   20.00             1/1/2015 01:01:00
002 20.00   40.00             1/1/2015 01:02:00
003 20.00   20.00             1/2/2015 00:00:00
003 10.00   30.00             1/3/2015 00:00:00
003 5.00    35.00             1/4/2015 00:00:00
0
2

With SQL Server 2012 or newer, you can use a WINDOW function with boundaries:

SELECT *
    , SUM([Amount]) OVER(PARTITION BY [ID] ORDER BY [date_time] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM @data;

With previous version, a self JOIN can be used:

SELECT d1.ID, d1.[date_time], d1.Amount
    , SUM(d2.Amount) as running_amount
FROM @data d1
INNER JOIN @data d2 ON d1.ID = d2.ID AND d2.[date_time] <= d1.[date_time]
GROUP BY d1.ID, d1.[date_time], d1.Amount;

Output:

ID  | date_time                 | Amount    | running_amount
1   | 2015-01-01 02:01:00.000   | 9         | 9
1   | 2015-01-02 02:02:00.000   | 20        | 29
1   | 2015-01-02 02:03:00.000   | 20        | 49
2   | 2015-01-01 02:01:00.000   | 20        | 20
2   | 2015-01-01 02:02:00.000   | 20        | 40
3   | 2015-01-02 01:00:00.000   | 20        | 20
3   | 2015-01-03 01:00:00.000   | 10        | 30
3   | 2015-01-04 01:00:00.000   | 5         | 35
2
  • what is a self join? I have never used that before. not exactly sure how to incorporate d1 and d2. I only have one table. – Kbert Mar 9 '16 at 14:50
  • a join to itself. here: FROM data INNER JOIN data (versus FROM t1 JOIN t2) – Julien Vavasseur Mar 9 '16 at 14:50
1

one version using correlated subquery..

select 
id,
amount
(select sum(amount) from test t2 where t1.id=t2.id and t1.datetime<=t2.datetime) as 'Runningamount',
datetime
from test t1

one using cross apply

select 
id,amount,b.*,datetime
from test t1
cross apply
(select sum(amount) as 'RA' from test t2 where t1.id=t2.id and t1.datetime<=t2.datetime) b

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.