30 day moving sum using the OVER clause in SQL server 2012

Question

I am trying to write a view in SQL Server 2012 where I calculate the 30 day moving sum for a set of transactions.

Current script:

SELECT CustNo, TransactionDate, TransactionAmount, SUM(TransactionAmount) 
        OVER (PARTITION BY CustNo ORDER BY TransactionDate) AS MovingAmount
FROM         dbo.TransactionData

Data set:

 CustNo     TransactionDate    TransactionAmount
 1111       5/7/2015           3,000
 1111       5/14/2015          3,000
 2222       5/17/2015          100
 1111       5/21/2015          3,000
 1111       5/28/2015          3,000
 3333       5/31/2015          11,000
 1111       6/10/2015          3,000

Expected Result:

 CustNo     TransactionDate    TransactionAmount   MovingAmount
 1111       5/7/2015           3,000               12,000 
 1111       5/14/2015          3,000               12,000
 2222       5/17/2015          100                 100
 1111       5/21/2015          3,000               9,000
 1111       5/28/2015          3,000               6,000
 3333       5/31/2015          11,000              11,000
 1111       6/10/2015          3,000               3,000

Other attempts:

SELECT CustNo, TransactionDate, TransactionAmount, SUM(TransactionAmount) 
        OVER (PARTITION BY CustomerNumber ORDER BY TransactionDate, 
        BETWEEN TransactionDate AND DATEADD(day, 30, TransactionDate)) 
        AS MovingAmount
FROM         dbo.TransactionData

I don't understand the logic you apply to get expected results — Horaciux, Aug 22 '17 at 14:58
Why is the moving amount is 12,000 on Transaction date 5/7/2015? It should be 3,000 from this sample data — Zohar Peled, Aug 22 '17 at 14:58
Zohar, it is aggregating the sum of transactions over 30 days, so it adds 3000 on 5/7, 3000 on 5/14, 3000 on 5/21, and 3000 on 5/28. — J. Reed, Aug 22 '17 at 15:02
Yes, but 5/7 is the first date, so it should be 3000, then on 5/14 it should be 6000, on 5/21 should be 9000 and on 6/10 should be 12000... — Zohar Peled, Aug 22 '17 at 15:07
I think what you want is not possible with an OVER clause. See here: https://stackoverflow.com/questions/10027192/sql-use-where-clause-in-over — Tab Alleman, Aug 22 '17 at 15:11
yeah the OVER will sum for all Transaction Amounts that match CustNo, i literally just learned this the other day as i was after something similar https://stackoverflow.com/questions/45795099/create-a-dynamic-sum-column-in-sql — PeterH, Aug 22 '17 at 15:41
@TabAlleman Thank you for responding! I will look for alternatives. — J. Reed, Aug 22 '17 at 16:54
@user91504 Thank you for responding! I will look for alternatives. — J. Reed, Aug 22 '17 at 16:54

Zohar Peled · Answer 1 · 2017-08-22T15:48:18.683

I'm assuming the 12,000 in the first row of your desired result is a typo, and it should be 3,000. If I'm correct, your first query seems to be working fine, it's just hard to see without an order by to the entire query:

Create and populate sample table (Please save us this step in your future questions)

CREATE TABLE TransactionData 
(
    CustNo int,
    TransactionDate date,
    TransactionAmount int
)

INSERT INTO TransactionData(CustNo, TransactionDate, TransactionAmount) VALUES
(1111, '2015-05-07', 3000),
(1111, '2015-05-14', 3000),
(2222, '2015-05-17', 100),
(1111, '2015-05-21', 3000),
(1111, '2015-05-28', 3000),
(3333, '2015-05-31', 11000),
(1111, '2015-06-10', 3000)

Going with Tab Alleman's interpretation of the question, It can't be done with the over clause, you need to use a correlated sub query:

SELECT  CustNo, 
        TransactionDate, 
        TransactionAmount, 
        (SELECT SUM(TransactionAmount) 
         FROM dbo.TransactionData t1 
         WHERE t1.CustNo = t0.CustNo
         AND t1.TransactionDate >= t0.TransactionDate
         AND t1.TransactionDate <= DATEADD(DAY, 30, t0.TransactionDate)) As MovingAmount
FROM dbo.TransactionData t0
ORDER BY CustNo, TransactionDate

Results:

CustNo  TransactionDate         TransactionAmount   MovingAmount
1111    07.05.2015 00:00:00     3000                12000
1111    14.05.2015 00:00:00     3000                12000
1111    21.05.2015 00:00:00     3000                9000
1111    28.05.2015 00:00:00     3000                6000
1111    10.06.2015 00:00:00     3000                3000
2222    17.05.2015 00:00:00     100                 100
3333    31.05.2015 00:00:00     11000               11000

You can see a live demo on rextester.

Except that this will SUM over the entire data set and not just 30 days moving. That's why you have 15000 in the fifth row, where, by your interpretation, you should only have 12000, since the July 5 transaction is outside the 30 day window. I also think your interpretation is to include 30 days PAST, where OP wants 30 days FUTURE. That would explain OP's desired result. — Tab Alleman, Aug 22 '17 at 15:08
@TabAlleman I guess you are correct and I simply misunderstood the question. — Zohar Peled, Aug 22 '17 at 15:43
@ZoharPeled Thank you very much! I will give this a shot. My apologies about not providing a proper data set. I will make sure to do that in the future. — J. Reed, Aug 22 '17 at 16:57

30 day moving sum using the OVER clause in SQL server 2012

1 Answers1