We help IT Professionals succeed at work.

rolling 12 months in sql 2005

918 Views
Last Modified: 2012-05-10
hi - I would like to know if possible the best way to calculate a rolling 12 month average from a set of data with a unique identifier.
if i have data in a table that resembles something like this...

unique_id      YYYYMM      total       rolling_annual_total
 2       200801      20      
 2       200802      30      
 2       200803      40      
 2       200804      20      
 2       200805      30      
 2       200806      -      
 2       200807      80      
 2       200808      70      
 2       200809      -      
 2       200810      20      
 2       200811      30      
 2       200812      45      
 2       200901      30      
 2       200902      60      
 2       200903      80      
 2       200904      10      
 2       200905      5      
 1       200801      10      
 1       200802      20      
 1       200803      30      
 1       200804      40      
 1       200805      50      
 1       200806      60      
 1       200807      20      
 1       200808      40      
 1       200809      60      
 1       200810      80      
 1       200811      35      
 1       200812      45      
 1       200901      20      
 1       200902      10      
 1       200903      15      
 1       200904      80      
 1       200905      100      

My data set has a unique identifier in the first column, how would i go about creating a rolling yearly total in the final column.
should i do a row number count and partition it by the un_id? or would it be best to convert the YYYYMM to datetime-12.
Any assistance would be greatly appreciated, cheers.
 
Comment
Watch Question

can you please show me what output do you want?
do you want output something like this?

yyyymm    Total  rolling_annual_total
200801    10    10
200802    20    30
200803    30    60
200804    40    100
200805    50    150
200806    60    210
200807    20    230
200808    40    270
200809    60    330
200810    80    410
200811    35    445
200812    45    490

create table TestQry
(
unique_id int,
YYYYMM varchar(10),
total int,
rolling_annual_total int
)
GO

insert into TestQry (unique_id,YYYYMM,total)
select  2 ,      '200801',      20   union all   
select   2,       '200802',      30         union all
 select  2,       '200803',      40         union all
 select  2,       '200804',      20         union all
 select  2,       '200805',      30         union all
 select  2,       '200806',      0         union all
 select  2,       '200807',      80         union all
 select  2,       '200808',      70         union all
 select  2,       '200809',      0         union all
 select  2,       '200810',      20         union all
 select  2,       '200811',      30         union all
 select  2,       '200812',      45         union all
 select  2,       '200901',      30         union all
 select  2,       '200902',      60         union all
 select  2,       '200903',      80         union all
 select  2,       '200904',      10         union all
 select  2,       '200905',      5         union all
 select  1,       '200801',      10         union all
 select  1,       '200802',      20         union all
 select  1,       '200803',      30         union all
 select  1,       '200804',      40         union all
 select  1,       '200805',      50         union all
 select  1,       '200806',      60         union all
 select  1,       '200807',      20         union all
 select  1,       '200808',      40         union all
 select  1,       '200809',      60         union all
 select  1,       '200810',      80         union all
 select  1,       '200811',      35         union all
 select  1,       '200812',      45         union all
 select  1,       '200901',      20         union all
 select  1,       '200902',      10         union all
 select  1,       '200903',      15         union all
 select  1,       '200904 ',     80         union all
 select  1,       '200905',      100         
 
 ;with cte as
 (
 select yyyymm,yr,MON,total,rolling_annual_total,rn from(
 select YYYYMM,LEFT(yyyymm,4) as yr,RIGHT(YYYYMM,2) as mon,total,total as rolling_annual_total,
 ROW_NUMBER() over(Order by LEFT(yyyymm,4),RIGHT(YYYYMM,2)) as rn  
 from TestQry where unique_id=1 and LEFT(yyyymm,4)='2008' and RIGHT(YYYYMM,2)='01'
 ) as t 
 
 union all
 
 select t1.yyyymm,t1.yr,t1.MON,t1.total,t1.total + c.rolling_annual_total as rolling_annual_total ,t1.rn from(
 select ts.YYYYMM,LEFT(ts.yyyymm,4) as yr,RIGHT(ts.YYYYMM,2) as mon,ts.total,
 ROW_NUMBER() over(Order by LEFT(ts.yyyymm,4),RIGHT(ts.YYYYMM,2)) as rn  
 from TestQry as ts where unique_id=1 and LEFT(yyyymm,4)='2008'
 ) as t1 join cte as c on t1.rn-1=c.rn where t1.rn>1
 
 )
 select yyyymm,total,rolling_annual_total from cte

Open in new window

Commented:
Try like this,

SELECT
      year(rtrim([YYYYMM])+'01')
     ,SUM(total)
FROM table1
group by year(rtrim([YYYYMM])+'01')

Author

Commented:
Hi RiteshShah
The output im looking for is a rolling average, so like this
yyyymm    Total  rolling_annual_total
200801    10    10
200802    20    15
200803    30    20
200804    40    25
200805    50    30
200806    60    35
200807    20    33
200808    40    34
200809    60    37

and so on for 12 months, and once at 12 months use a moving 12 month average.
If I use large data sets the 'union all' is not really practical.
you can use group by


example :

 select  sum(annual_total) from table1 group by field1, field2

Commented:
Here you go:

select t1.unique_id,t1.YYYYMM,total,t2.x as rolling_annual_total from Table_1 t1
left join (
select left(YYYYMM,4) AS YYYYMM, SUM(total) x
from Table_1
group by left(YYYYMM,4)
)t2
on left(t1.YYYYMM,4) = t2.YYYYMM

Commented:
try the below:
;with CTE as (
	select 	unique_id, 
		convert(datetime, cast(YYYYMM as varchar) + '01', 112) as YYYYMM,
		total
	from yourtable
)
select 	a.unique_id,
	a.[YYYYMM],
	a.total,
	(select avg(total) from CTE where [YYYYMM] <= a.[YYYYMM] and unique_id = a.unique_id) as rolling_anual_total
from CTE a

Open in new window

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
CORRECTION:

Left out one ISNULL() check:

SELECT tq1.unique_id, tq1.YYYYMM, tq1.total,
    CAST((tq1.total + ISNULL(pm.PriorMonthsTotal, 0)) / (1.0 + ISNULL(pm.#PriorMonths, 0)) AS int)
...

Author

Commented:
HI Scott
My database did not like the cross apply, I am using sql2005 but get the following error when running my query.

"id" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

any hints?

Author

Commented:
@ ralmada - this is essentially what i am after but when i reach 12 months (in this example YYYYMM=200911) I would like to keep a rolling average at 12 months i.e. sum yyyymm 201001 to  201012 and so on.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
I tested it on SQL2k5, worked fine for me :-) .

Did you create the function first?

Pls post the code you used.  It's impossible to debug air.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.