Solved

rolling 12 months in sql 2005

Posted on 2010-09-16
14
820 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.
 
0
Comment
Question by:twfw123
  • 3
  • 3
  • 3
  • +3
14 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 33689397
can you please show me what output do you want?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 33689530
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
 
LVL 2

Expert Comment

by:vsosu
ID: 33689639
Try like this,

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

Author Comment

by:twfw123
ID: 33690252
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
 

Expert Comment

by:sundara2010
ID: 33690611
you can use group by


example :

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

Expert Comment

by:vsosu
ID: 33691198
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
 
LVL 41

Expert Comment

by:ralmada
ID: 33692126
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 200 total points
ID: 33693764
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33693791
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
 

Author Comment

by:twfw123
ID: 33698886
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
 

Author Comment

by:twfw123
ID: 33699223
@ 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
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 200 total points
ID: 33701407
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33703852
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
 
LVL 2

Assisted Solution

by:vsosu
vsosu earned 100 total points
ID: 33713720
Did you try my query?
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now