Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 845
  • Last Modified:

rolling 12 months in sql 2005

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.
 
0
twfw123
Asked:
twfw123
  • 3
  • 3
  • 3
  • +3
3 Solutions
 
RiteshShahCommented:
can you please show me what output do you want?
0
 
RiteshShahCommented:
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

0
 
vsosuCommented:
Try like this,

SELECT
      year(rtrim([YYYYMM])+'01')
     ,SUM(total)
FROM table1
group by year(rtrim([YYYYMM])+'01')
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
twfw123Author 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.
0
 
sundara2010Commented:
you can use group by


example :

 select  sum(annual_total) from table1 group by field1, field2
0
 
vsosuCommented:
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
0
 
ralmadaCommented:
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

0
 
Scott PletcherSenior DBACommented:
You will almost certainly *require* an index on (unique_id, YYYYMM) to get good performance for what you need to do.  If the index is not clus [clus would be much better], you will need to INCLUDE the total column in the index.

First, create the function (once).  I made the months variable instead of hard-coding 12 -- naturally you could remove the #months and hard-code 12 instead.

Then run the CROSS APPLY query (as much as needed :-) ).
CREATE FUNCTION dbo.GetPriorMonths (
    @unique_id int,
    @YYYYMM char(6),
    @#months int
)
RETURNS TABLE
AS
RETURN (
SELECT COUNT(*) AS [#PriorMonths], SUM(total) AS PriorMonthsTotal
FROM TestQry
WHERE unique_id = @unique_id
  AND YYYYMM BETWEEN CONVERT(char(6), DATEADD(MONTH, -@#months + 1, @YYYYMM + '01'), 112) AND
    CONVERT(char(6), DATEADD(MONTH, -1, @YYYYMM + '01'), 112)    
)
GO


SELECT tq1.unique_id, tq1.YYYYMM, tq1.total,
    CAST((tq1.total + ISNULL(pm.PriorMonthsTotal, 0)) / (1.0 + pm.#PriorMonths) AS int)
FROM TestQry tq1
OUTER APPLY dbo.GetPriorMonths (unique_id, YYYYMM, 12) AS pm
ORDER BY 1, 2

Open in new window

0
 
Scott PletcherSenior DBACommented:
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)
...
0
 
twfw123Author 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?
0
 
twfw123Author 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.
0
 
ralmadaCommented:
You lost me there.
Do you mean you want the rolling average for the last 12 months, say that now we are in september, so you want the rolling average from Oct/2009 to Sep/2010? If so, then try

;with CTE as (
	select 	unique_id, 
		convert(datetime, cast(YYYYMM as varchar) + '01', 112) as YYYYMM,
		total
	from yourtable
         where convert(datetime, cast(YYYYMM as varchar) + '01', 112) between dateadd(m, datediff(m, 0, getdate())-12, 0) and dateadd(m, datediff(m, 0, getdate()), 0)
)
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

0
 
Scott PletcherSenior DBACommented:
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.
0
 
vsosuCommented:
Did you try my query?
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now