moving average of table for last 2 years

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 2008 Forums

Transact-SQL (2008)

moving average of table for last 2 years

Author

Topic

Junior Sqler
Starting Member

18 Posts

Posted - 2013-06-19 : 07:10:54

hello!

I have a table Initial that includes costs for 3 years:2010-2012.
Columns are following:
1.Department (e.g A), 2.Transactiondate (eg 2012-03-01) , 3.Cost ( e.g 5$)

For each day of year 2012 i would like to calculate the average cost (moving average) based on data from last 2 years (730 days)

My query is the following:

DECLARE @CurrentDate Date
set @CurrentDate = '2012-01-01'
DECLARE @EndDate Date
set @EndDate = '2012-12-31'

while @CurrentDate < @EndDate
BEGIN

drop table #average

select a.departmentcode,@CurrentDate as transactiondate,
avg(amountofinvoiceUSD) as AverageAmount
into #average
from Initial a
where a.transactiondate<=@CurrentDate and transactiondate>= DATEADD(DAY, -730, @CurrentDate)
group by departmentcode,@CurrentDate

set @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
end

Folowing message appears:Each GROUP BY expression must contain at least one column that is not an outer reference.

Where is the problem?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-19 : 07:25:48

Why use a loop?
You can probably get it right like this in a set based method:

select a.departmentcode,a.TransactionDate as transactiondate,
avg(b.amountofinvoiceUSD) as AverageAmount
into #average
from Initial a
inner join initial b
on a.departmentcode = b.departmentcode
and a.transactiondate >= DATEADD(DAY, -730, b.transactiondate)
where a.Transactiondate >= '2012-01-01'
group by a.departmentcode, a.transactiondate

Duane.
http://ditchiecubeblog.wordpress.com/

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-19 : 08:14:32

BTW.... I think the reason for the error is because the variable is being used in the group by.
Essentially you don't need to include it there as it will be just 1 value each time the query executes when using this approach.

But you should try avoid this approach as much as possible and rather look at a set based approach, as indicated above.

Duane.
http://ditchiecubeblog.wordpress.com/

Junior Sqler
Starting Member

18 Posts

Posted - 2013-06-19 : 08:34:52

ook..i did it like the following.
now another issue is that it starts from year 2012 and in years 2010 and 2011 moving average is null..
and it puts at the firts day of 2012 the average of first day of 2010, second day of 2012 the average of second day of 2010 etc..
any ideas?

drop table #a
select a.departmentcode,a.TransactionDate as transactiondate
,avg(b.amountofinvoiceUSD) as AverageAmount
into #a
from invoice12 a
left join invoice12 b
on a.departmentcode = b.departmentcode

where b.transactiondate <DATEADD(DAY, -730, a.transactiondate)
group by a.departmentcode, a.transactiondate,b.departmentcode,b.transactiondate
order by departmentcode,transactiondate
select * from #a

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-19 : 08:48:46

Does this not do it?

drop table #a
select a.departmentcode,a.TransactionDate as transactiondate
,avg(b.amountofinvoiceUSD) as AverageAmount
into #a
from invoice12 a
left join invoice12 b
on a.departmentcode = b.departmentcode and
b.transactiondate between DATEADD(DAY, -730, a.transactiondate) AND a.TransactionDate

where a.transactiondate >= '2012-01-01'
group by a.departmentcode,a.TransactionDate
order by a.departmentcode,a.TransactionDate
select * from #a

Duane.
http://ditchiecubeblog.wordpress.com/

Junior Sqler
Starting Member

18 Posts

Posted - 2013-06-19 : 09:21:46

It's working :)
Thank you!!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-19 : 09:38:14

Great.
;)

Duane.
http://ditchiecubeblog.wordpress.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources