Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

rolling 12 months in sql 2005

Posted on 2010-09-16
14
830 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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
 
LVL 69

Accepted Solution

by:
Scott Pletcher 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:Scott Pletcher
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:Scott Pletcher
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

808 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