Calculate preceding (rolling) 12 month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculate preceding (rolling) 12 month

Author

Topic

Irina
Starting Member

3 Posts

Posted - 2006-02-28 : 12:10:38

I have 3 Years Revenue data in the db. I need to find a SUM(Revenue) of rolling 12 month.Data is loaded every month. Next time Feb/2006 will be loaded. I need to use it in the stored procedure for on line reporting. Any help would be appreciated.

Data

id Y R_01 R_02 R_03 R_03 R_04 R_05 R_06 R_07 R_08 R_09 R_10 r_11 R_12
12 06 100
12 05 100 100 100 100 100 100 100 100 100 100 100 100 100
12 04 100 100 100 100 100 100 100 100 100 100 100 100 100

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-28 : 12:44:09

To do this you should change your schema to a normalized format:

id|year|month|value
12|2005|01 |100
12|2005|02 |100
12|2005|03 |100

Irina
Starting Member

3 Posts

Posted - 2006-03-01 : 10:18:25

Thanks for advice. But the amount of data is too big to be pivoted on the fly. To find a SUM(Revenue) for all customers is just a first step. If current month is 01/2006 I need to add 11 month data from previous year + current month data and it should be dynamic calculation for each month. I can always use CASE but I am looking for some function, I know Oracle has one.

ismail_issac
Starting Member

22 Posts

Posted - 2006-03-01 : 11:03:32

hi

try this

Create view SumRevenew
as
select Y,R_01,1 as mnth from TABLENAME group by Y,R_01
union
select Y,R_02,2 from TABLENAME group by Y,R_02
union
select Y,R_03,3 from TABLENAME group by Y,R_03
union
select Y,R_04,4 from TABLENAME group by Y,R_04
union
select Y,R_05,5 from TABLENAME group by Y,R_05
union
select Y,R_06,6 from TABLENAME group by Y,R_06
union
select Y,R_07,7 from TABLENAME group by Y,R_07
union
select Y,R_08,8 from TABLENAME group by Y,R_08
union
select Y,R_09,9 from TABLENAME group by Y,R_09
union
select Y,R_10,10 from TABLENAME group by Y,R_10
union
select Y,R_11,11 from TABLENAME group by Y,R_11
union
select Y,R_12,12 from TABLENAME group by Y,R_12

then execute the following to obtain the sum .....

Select sum(R_01) From SumRevenew Where Y = Year(GetDate()) - 1 And mnth >= Month(GetDate()) + 1
Or (Y = Year(GetDate()) And mnth <= Month(GetDate()))

I think this should solve your purpose

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-01 : 20:33:10

Here you go. Using ismail_issac suggested method

create table #data
(
	id	int,
	Yr	int,
	R_01 	int,
	R_02 	int,
	R_03 	int,
	R_04 	int,
	R_05 	int,
	R_06 	int,
	R_07 	int,
	R_08 	int,
	R_09 	int,
	R_10 	int,
	R_11 	int,
	R_12	int
)

insert into #data
select	12, 2004, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412	union all
select	12, 2005, 501, 502, 503, 504, 505, 506, 507, 508, 509, 510, 511, 512	union all
select	12, 2006, 601, 602,   0,   0,   0,   0,   0,   0,   0,   0,   0,   0	

----

declare
	@start	datetime,
	@end	datetime

select	@end	= '2006-01-01'
select	@start	= dateadd(month, -11, @end)

select	sum(R)
from
(
select 	dateadd(year, Yr - 1900, '1900-01-01') as dte, R_01 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end) union all
select 	dateadd(year, Yr - 1900, '1900-02-01') as dte, R_02 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end) union all
select 	dateadd(year, Yr - 1900, '1900-03-01') as dte, R_03 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end) union all
select 	dateadd(year, Yr - 1900, '1900-04-01') as dte, R_04 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end) union all
select 	dateadd(year, Yr - 1900, '1900-05-01') as dte, R_05 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end) union all
select 	dateadd(year, Yr - 1900, '1900-06-01') as dte, R_06 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end) union all
select 	dateadd(year, Yr - 1900, '1900-07-01') as dte, R_07 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end) union all
select 	dateadd(year, Yr - 1900, '1900-08-01') as dte, R_08 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end) union all
select 	dateadd(year, Yr - 1900, '1900-09-01') as dte, R_09 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end) union all
select 	dateadd(year, Yr - 1900, '1900-10-01') as dte, R_10 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end) union all
select 	dateadd(year, Yr - 1900, '1900-11-01') as dte, R_11 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end) union all
select 	dateadd(year, Yr - 1900, '1900-12-01') as dte, R_12 as R from #data where id = 12 and Yr >= year(@start) and Yr <= year(@end)
) as n
where	n.dte	>= @start
and	n.dte	<= @end

What you required (rolling 12 months sum) can be very simplified if your table is normalized. What those codes in blue color does is restructure the unnormalize form into normalize form.

But seriously you should consider normalize your data

id|date    |value
12|20050101|100
12|20050201|100
12|20050301|100

Personally I prefer this way as you don't have to break apart the year and month and simplify your where clause
example :

where date between start_date and end_date

where date >= start_date
and   date <= end_date

----------------------------------
'KH'

Irina
Starting Member

3 Posts

Posted - 2006-03-02 : 10:46:46

I found the solution which works for my tables very nicely.
I case somebody need this calculation:

DECLARE @id int, @month int, @year int

SELECT @id = 2, @month = 5, @year = 2006

SELECT
(select revenue_01 from revenue where id = @id and year = @year) +
(select revenue_02 from revenue where id = @id and year = case when 2 > @month then @year - 1 else @year end) +
(select revenue_03 from revenue where id = @id and year = case when 3 > @month then @year - 1 else @year end) +
(select revenue_04 from revenue where id = @id and year = case when 4 > @month then @year - 1 else @year end) +
(select revenue_05 from revenue where id = @id and year = case when 5 > @month then @year - 1 else @year end) +
(select revenue_06 from revenue where id = @id and year = case when 6 > @month then @year - 1 else @year end) +
(select revenue_07 from revenue where id = @id and year = case when 7 > @month then @year - 1 else @year end) +
(select revenue_08 from revenue where id = @id and year = case when 8 > @month then @year - 1 else @year end) +
(select revenue_09 from revenue where id = @id and year = case when 9 > @month then @year - 1 else @year end) +
(select revenue_10 from revenue where id = @id and year = case when 10 > @month then @year - 1 else @year end) +
(select revenue_11 from revenue where id = @id and year = case when 11 > @month then @year - 1 else @year end) +
(select revenue_12 from revenue where id = @id and year = case when 12 > @month then @year - 1 else @year end)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources