Rolling Sum SQL Server pre-2012

Question

I have a question regarding the rolling sum in SQL Server Pre-2012.

as you can see from the text below, I have a serious of money flows on a monthly basis and I would like to calculate the rolling sum in order to achieve different time horizon (e.g. for 1 year I would like to sum the first twelve and rolling..).

In SQL Server after 2012 there is the "partition" command with the "order by" which manage to do the job. What if I am using a pre-2012 version?

Hope everything is clear, otherwise I will try to rephrase the question!

id;date;count period;datatype;Time_Horizon;1 month
abcd;31/01/2015;1.00;Money;1M;-3.817794537
abcd;28/02/2015;2.00;Money;1M;4.725421751
abcd;31/03/2015;3.00;Money;1M;0.999422223
abcd;30/04/2015;4.00;Money;1M;-3.785429484
abcd;31/05/2015;5.00;Money;1M;-0.037784528
abcd;30/06/2015;6.00;Money;1M;1.35447345
abcd;31/07/2015;7.00;Money;1M;-1.154258508
abcd;31/08/2015;8.00;Money;1M;-0.322592199
abcd;30/09/2015;9.00;Money;1M;-1.496497343
abcd;31/10/2015;10.00;Money;1M;-4.901709438
abcd;30/11/2015;11.00;Money;1M;4.275774672
abcd;31/12/2015;12.00;Money;1M;-8.743591429
abcd;31/01/2016;13.00;Money;1M;0.093734273
abcd;29/02/2016;14.00;Money;1M;-5.226279991
abcd;31/03/2016;15.00;Money;1M;-2.971203457
abcd;30/04/2016;16.00;Money;1M;-1.088491226
abcd;31/05/2016;17.00;Money;1M;-1.380297903
abcd;30/06/2016;18.00;Money;1M;-0.432389513
abcd;31/07/2016;19.00;Money;1M;-0.773134267
abcd;31/08/2016;20.00;Money;1M;-0.699732667
abcd;30/09/2016;21.00;Money;1M;-0.816940324
abcd;31/10/2016;22.00;Money;1M;-1.737192168
abcd;30/11/2016;23.00;Money;1M;-5.006184229
abcd;31/12/2016;24.00;Money;1M;-6.735075861
abcd;31/01/2017;25.00;Money;1M;-0.574752036
abcd;28/02/2017;26.00;Money;1M;-1.426153523
abcd;31/03/2017;27.00;Money;1M;-3.995159675
abcd;30/04/2017;28.00;Money;1M;-1.966250762
abcd;31/05/2017;29.00;Money;1M;-1.804032909
abcd;30/06/2017;30.00;Money;1M;-2.016144093
abcd;31/07/2017;31.00;Money;1M;-1.475106552
abcd;31/08/2017;32.00;Money;1M;-1.860843233
abcd;30/09/2017;33.00;Money;1M;-1.19544486
abcd;31/10/2017;34.00;Money;1M;-10.41490183
abcd;30/11/2017;35.00;Money;1M;-1.701649381
abcd;31/12/2017;36.00;Money;1M;-7.037158969
abcd;31/01/2018;37.00;Money;1M;-5.021293226
abcd;28/02/2018;38.00;Money;1M;-2.063987708
abcd;31/03/2018;39.00;Money;1M;-1.327867698
abcd;30/04/2018;40.00;Money;1M;-2.555916988
abcd;31/05/2018;41.00;Money;1M;-0.825325207
abcd;30/06/2018;42.00;Money;1M;-3.529128638
abcd;31/07/2018;43.00;Money;1M;-0.64903325
abcd;31/08/2018;44.00;Money;1M;0.755237218
abcd;30/09/2018;45.00;Money;1M;-1.645666712
abcd;31/10/2018;46.00;Money;1M;-2.654650057
abcd;30/11/2018;47.00;Money;1M;-1.083287292

From file attached? There isn't an attached file (you can't attach files on SO), however, don't provide files, provide **formatted** `text` data or DDL and DML statements. — Thom A, Dec 14 '18 at 09:09
[Solving the Running Total and Ordinal Rank Problems (Rewritten)](http://www.sqlservercentral.com/articles/T-SQL/68467/) — Thom A, Dec 14 '18 at 09:11
Considering, however, that SQL Server 2008 is basically complete out of support, perhaps the conversation you should be having is "when and how are we upgrading our server?" — Thom A, Dec 14 '18 at 09:13
I have edited the post showing the data I am working on. Would it be useful? — Steven Ingo, Dec 14 '18 at 09:13
Semicolon delimited isn't great, but it's *usable*. Have a look at the article I linked though. — Thom A, Dec 14 '18 at 09:13
Show the code that works in SQL Server 2012. It would help explain what you want to do. — Gordon Linoff, Dec 14 '18 at 11:41

score 0 · Answer 1 · answered Dec 14 '18 at 11:43

In earlier versions of SQL Server, you would use apply. For instance, for a 12 row rolling sum:

select t.*, t2.amount
from t cross apply
     (select sum(t2.amount) as amount
      from (select top (12) t2.*
            from t t2
            where t2.col1 = t.col1 and
                  t2.month <= t.month
           ) t2
     ) t2;

For the 12 month rolling sum:

select t.*, t2.amount
from t cross apply
     (select sum(t2.amount) as amount
      from t t2
      where t2.col1 = t.col1 and
            t2.month <= t.month and
            t2.month > dateadd(month, -12, t.month)
     ) t2;

Rolling Sum SQL Server pre-2012

1 Answers1