SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 moving average of table for last 2 years
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Junior Sqler
Starting Member

18 Posts

Posted - 06/19/2013 :  07:10:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/19/2013 :  07:25:48  Show Profile  Visit ditch's Homepage  Reply with Quote
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/

Edited by - ditch on 06/19/2013 07:35:52
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/19/2013 :  08:14:32  Show Profile  Visit ditch's Homepage  Reply with Quote
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/
Go to Top of Page

Junior Sqler
Starting Member

18 Posts

Posted - 06/19/2013 :  08:34:52  Show Profile  Reply with Quote
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
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/19/2013 :  08:48:46  Show Profile  Visit ditch's Homepage  Reply with Quote
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/
Go to Top of Page

Junior Sqler
Starting Member

18 Posts

Posted - 06/19/2013 :  09:21:46  Show Profile  Reply with Quote
It's working :)
Thank you!!
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/19/2013 :  09:38:14  Show Profile  Visit ditch's Homepage  Reply with Quote
Great.
;)


Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000