We help IT Professionals succeed at work.

rolling 12 months in sql 2005

on
918 Views
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

View Solutions Only

Commented:
can you please show me what output do you want?

Commented:
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
``````

Commented:
Try like this,

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

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.

Commented:
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
``````
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)
Senior 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)
...

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?

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)
Senior 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)

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

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

Deciding to stick with EE.

Mohamed Asif

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

Carl Webster
CTP, Sr Infrastructure Consultant
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

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

• Troubleshooting
• Research
• Professional Opinions
Unlock the solution to this question.