SQL Server adding numbers between rows without cursor [duplicate]

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
– Jonast92, Commented Jun 3, 2014 at 14:57
@Jonast92 he wants a running total, so 26 x 3 is basically 26 + 0 + 0 — Darren Kopp
– Darren Kopp, Commented Jun 3, 2014 at 14:58
Take a look at sqlfiddle.com/#!3/48508/11 - I'm not sure if that answers your question, it's an alternative to the answer posted below. — Brent
– Brent, Commented Jun 3, 2014 at 15:11

Gordon Linoff · Accepted Answer · 2014-06-03 15:00:17Z

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 3, 2014 at 15:00

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user441521 Over a year ago

Checking this out now

Collectives™ on Stack Overflow

SQL Server adding numbers between rows without cursor [duplicate]

data

result

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

data

result

1 Answer 1

1 Comment

Linked

Related