SQL Server adding numbers between rows without cursor

Question

I'm wondering if there is any sort of function that can help me with adding numbers between rows. I'm not sure how to best explain it, but here is an example of the initial data and the result I'm looking for. I have a ton of records so I'm looking for the fastest way to do this as I have about 8 million records like this.

data

Mike, Smith, 1/1/2014, 26
Mike, Smith, 2/1/2014, 0
Mike, Smith, 3/1/2014, 0
Mike, Smith, 4/1/2014, -2
Mike, Smith, 5/1/2014, 0
Mike, Smith, 5/1/2014, 3
Joe, Blow, 1/1/2014, 15
Joe, Blow, 2/1/2014, 0
Joe, Blow, 3/1/2014, 2

result

Mike, Smith, 1/1/2014, 26
Mike, Smith, 2/1/2014, 26
Mike, Smith, 3/1/2014, 26
Mike, Smith, 4/1/2014, 24
Mike, Smith, 5/1/2014, 24
Mike, Smith, 5/1/2014, 27
Joe, Blow, 1/1/2014, 15          <-- note that because it's unique first/last we start over with our adding
Joe, Blow, 2/1/2014, 15
Joe, Blow, 3/1/2014, 17

The idea is to keep adding based on the first number by a certain group (first name, last name, date in this case) and order (dt in this case). I have other names with the same repeating dates but it would need to start over for each unique name with adding.

You need to be more clear. 26 x 3 on Mike, Smith and then 2 other ids for him? And 15 x 2 on Joe, Blow and then 1 other id, there is no pattern here to work with. — Jonast92, Jun 03 '14 at 14:57
Refer to [this article](http://sqlperformance.com/2012/07/t-sql-queries/running-totals) by SO user Aaron B — rs., Jun 03 '14 at 14:58
@Jonast92 he wants a running total, so 26 x 3 is basically 26 + 0 + 0 — Darren Kopp, Jun 03 '14 at 14:58
Take a look at http://sqlfiddle.com/#!3/48508/11 - I'm not sure if that answers your question, it's an alternative to the answer posted below. — Brent, Jun 03 '14 at 15:11

score 4 · Accepted Answer · answered Jun 03 '14 at 15:00

4

What you seem to want is the cumulative sum. This is available easily in SQL Server 2012:

select t.*, sum(val) over (partition by firstname, lastname order by date) as cumsum
from table t;

You can do similar things in earlier versions of SQL Server, but the syntax is more cumbersome.

answered Jun 03 '14 at 15:00

Gordon Linoff

1,198,228
53
572
709

Checking this out now – user441521 Jun 03 '14 at 15:12
Worked like a charm! Thanks – user441521 Jun 03 '14 at 15:22

SQL Server adding numbers between rows without cursor

data

result

1 Answers1