August 23, 2016 at 6:01 am
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.
August 23, 2016 at 6:14 am
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?
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
August 23, 2016 at 6:23 am
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
August 23, 2016 at 6:54 am
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.
August 23, 2016 at 6:58 am
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?
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
August 23, 2016 at 7:07 am
yes, if you don't mind. I'm doing a few other things and had to put this aside.
August 23, 2016 at 7:12 am
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
August 23, 2016 at 7:18 am
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,
August 23, 2016 at 7:19 am
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.
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
August 23, 2016 at 7:38 am
plenty of ideas and thoughts to be found here
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 23, 2016 at 7:58 am
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
August 23, 2016 at 8:07 am
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
August 23, 2016 at 8:10 am
J Livingston SQL (8/23/2016)
plenty of ideas and thoughts to be found here
... and then I remembered the syntax for "LAG" which is also very useful for this I think
Many thanks
August 23, 2016 at 1:12 pm
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