SQL to sum last 12 month values from same table

  • What's the quickest way to sum "last 12 months" values from one date column and update 2nd column?

    e.g. original data contains "Date" and "Value", and we need to update new field "Date2" with the sum of "last 12 months" values.

    so goal would be (very simple example)

    Date Value Date2

    ------------- ------ --------

    01/08/2016 100 1200

    01/07/2016 100 1200

    01/06/2016 100 1150

    01/05/2016 100

    01/04/2016 100

    01/03/2016 100

    01/02/2016 100

    01/01/2016 100

    01/12/2015 100

    01/11/2015 100

    01/10/2015 100

    01/09/2015 100

    01/08/2015 100

    01/07/2015 50

    I've only populated first 3 rows of Date2 as the source data only goes back to 01/07/2015 in this example.

    My thoughts were to use row(over partition ...), and join back to same table.

  • DuncEduardo (8/23/2016)


    What's the quickest way to sum "last 12 months" values from one date column and update 2nd column?

    e.g. original data contains "Date" and "Value", and we need to update new field "Date2" with the sum of "last 12 months" values.

    so goal would be (very simple example)

    Date Value Date2

    ------------- ------ --------

    01/08/2016 100 1200

    01/07/2016 100 1200

    01/06/2016 100 1150

    01/05/2016 100

    01/04/2016 100

    01/03/2016 100

    01/02/2016 100

    01/01/2016 100

    01/12/2015 100

    01/11/2015 100

    01/10/2015 100

    01/09/2015 100

    01/08/2015 100

    01/07/2015 50

    I've only populated first 3 rows of Date2 as the source data only goes back to 01/07/2015 in this example.

    My thoughts were to use row(over partition ...), and join back to same table.

    Almost...have you checked out SUM() OVER?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • This is a simple running totals problem. If you search these words you will get relevant hits: running total sql server itzik

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • I wouldn't say it's completely a "running total" problem as you require only previous 12 records from the one in scope.

    Running Total will keep adding next value but not select a particular start value/row.

  • DuncEduardo (8/23/2016)


    I wouldn't say it's completely a "running total" problem as you require only previous 12 records from the one in scope.

    Running Total will keep adding next value but not select a particular start value/row.

    The solutions in SQL Server 2014 are only trivially different. Do you still require help with this?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • yes, if you don't mind. I'm doing a few other things and had to put this aside.

  • in the past, I'd have used embedded SQL, a variable, and WHILE construct but seems bit overkill for this.

    bit rusty with Windows functions

  • DuncEduardo (8/23/2016)


    yes, if you don't mind. I'm doing a few other things and had to put this aside.

    If you can post some DDL and Sample Data that would be very helpful. Lots of folks here will be more willing to help.

    Cheers,


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • DuncEduardo (8/23/2016)


    in the past, I'd have used embedded SQL, a variable, and WHILE construct but seems bit overkill for this.

    bit rusty with Windows functions

    Sure no problem.

    Can you set up your sample data set please, as a CREATE TABLE with a bunch of INSERTs to populate it? You might want to extend the set to say 20 rows and add a little variety to the quantities. Cheers.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • plenty of ideas and thoughts to be found here

    https://www.simple-talk.com/sql/t-sql-programming/calculating-values-within-a-rolling-window-in-transact-sql/

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • DuncEduardo (8/23/2016)


    I wouldn't say it's completely a "running total" problem as you require only previous 12 records from the one in scope.

    Running Total will keep adding next value but not select a particular start value/row.

    Actually it can with the Windowing Function enhancements found in 2012+. You can now add a "frame" to the windowing function, so 12-month-rolling-running-totals are very simple once you know the syntax (and can visualize what is really happening). I love Fabiano Amorim's blog posts for that:

    https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server/

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • ChrisM@Work (8/23/2016)


    DuncEduardo (8/23/2016)


    in the past, I'd have used embedded SQL, a variable, and WHILE construct but seems bit overkill for this.

    bit rusty with Windows functions

    Sure no problem.

    Can you set up your sample data set please, as a CREATE TABLE with a bunch of INSERTs to populate it? You might want to extend the set to say 20 rows and add a little variety to the quantities. Cheers.

    I had forgotten how to specify a range within the windows function but after a bit of MSDNing it twigged.

    Then remembered "preceding" and "current row".

    So, basic syntax (this allows me to calculate the Retention% against each MonthDate, which is calculated by summing previous 11 months, including current :

    select

    monthdate

    ,Leavers

    ,RTM = case when row_number() over (order by monthdate) > 11

    then sum(Leavers) over (order by monthdate rows between 11 preceding and current row)

    end

    from Headcount

    I think that works ok.

    DDL:

    create table Headcount(PK_S int identity(1,1) primary key, MonthDate datetime, Leavers int, RTM int)

    insert Headcount(MonthDate, Leavers)

    select '20160801', 23

    union select '20160701', 67

    union select '20160601', 80

    union select '20160501', 70

    union select '20160401', 104

    union select '20160301', 79

    union select '20160201', 28

    union select '20160101', 45

    union select '20151201', 46

    union select '20151101', 61

    union select '20151001', 53

    union select '20150901', 63

    union select '20150801', 61

    union select '20150701', 44

    union select '20150601', 31

    union select '20150501', 64

    union select '20150401', 55

    union select '20150301', 54

    union select '20150201', 46

    union select '20150101', 44

    union select '20141201', 112

    union select '20141101', 24

    union select '20141001', 38

    union select '20140901', 40

    union select '20140801', 60

  • J Livingston SQL (8/23/2016)


    plenty of ideas and thoughts to be found here

    https://www.simple-talk.com/sql/t-sql-programming/calculating-values-within-a-rolling-window-in-transact-sql/

    ... and then I remembered the syntax for "LAG" which is also very useful for this I think

    Many thanks

  • No need to LAG. Summary Functions are windowed too. It reads much cleaner.

    select PK_S, MonthDate,Leavers

    ,SUM(Leavers) OVER(ORDER BY PK_S DESC

    ROWS BETWEEN CURRENT ROW AND 11 FOLLOWING) as RTM

    from Headcount

    order by PK_S

    set statistics time,io off;

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply