1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user441521
  • 6,942
  • 23
  • 88
  • 160
  • 1
    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
  • 2
    @Jonast92 he wants a running total, so 26 x 3 is basically 26 + 0 + 0 – Darren Kopp Jun 03 '14 at 14:58
  • What version of SQL Server are you using? – Gordon Linoff Jun 03 '14 at 15:01
  • 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

1 Answers1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786