Search code examples
sqlsap-ase

SQL Running total previous 3 months by date and id

This is a simplification of the table q3 I'm working with:

      Partno EndOfMonth AA AS EA ES
        a    31.5.2017   5  1  0  1
        b    31.5.2017   3  1  0  1
        c    31.5.2017   2  2  0  1
        a    31.6.2017   1  2  2  2
        b    31.6.2017   1  0  1  2
        c    31.6.2017   2  3  1  4
        a    31.7.2017   4  3  2  0
        b    31.7.2017   3  0  6  0
        c    31.7.2017   4  1  0  0

I need to sum the numbers in the last four columns for each part in Partno so that the sum represents the running total of the last three months at each date in the EndOfMonth column.

The result i'm looking for is:

      Partno EndOfMonth AA AS EA ES
        a    31.5.2017   5  1  0  1
        b    31.5.2017   3  1  0  1
        c    31.5.2017   2  2  0  1
        a    31.6.2017   6  3  2  3
        b    31.6.2017   6  1  1  3
        c    31.6.2017   4  5  1  5
        a    31.7.2017   10 6  4  3
        b    31.7.2017   7  1  7  3
        c    31.7.2017   8  6  1  5

So e.g. for partno A at 31.7.2017 the last thee months' sum for the 'AA' column is 4+1+5=10.

I'm quite new to SQL and am well and truly stuck with this. I've tried something like the following to just get a simple rolling total (without even specifying the sum range to be the last 3 months). Also, I'm not sure if the database even supports all the functions in the below code, since it's giving me the error "Incorrect Syntax near the keyword 'OVER'"

  SELECT
    Partno,
    EndofMonth,
    SUM(q3.AA) OVER (PARTITION BY q3.Partno ORDER BY EndofMonth ROWS UNBOUNDED PRECEDING) as 'AA'
FROM q3

Anyway, any help would be greatly appreciated!

Thanks

EDIT:

Thanks to Benjamin and with a little help from this post: https://dba.stackexchange.com/questions/114403/date-range-rolling-sum-using-window-functions I was able to find the solution:

SELECT a.Partno, a.EndofMonth, SUM(b.AA) as 'AA', SUM(b.AS) as 'AS',... 
FROM q3 a, q3 b
WHERE a.Partno = b.Partno AND a.endOfMonth >= b.endOfMonth
    AND b.endOfMonth >= DATEADD(month,-2,a.endOfMonth)
GROUP BY a.Partno, a.endOfMonth
Solution

  • Something like this might work:

    SELECT a.Partno, a.EndofMonth, SUM(b.AA) as AA
FROM q3 a, q3 b
WHERE a.Partno = b.Partno
    AND DATEDIFF(month, b.endOfMonth, a.endOfMonth) < 4
GROUP BY a.Partno, b.Partno

    This assumes that endOfMonth is in datetime format, if it is not you will have to use convert(). Note that you might have to replace DATEDIFF() depending on what implementation you are using.

    I haven't tested this, so I might be way off. It has been a while since I worked with SQL. Hopefully you can get it working by messing around with it a bit, and if not then maybe it will inspire you to write something better. Let me know how it goes!