How to Calculate Weigheted Average by year (a date

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

General SQL Server Forums

New to SQL Server Programming

How to Calculate Weigheted Average by year (a date

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2010-08-22 : 22:53:07

hello All,

i am in a weired situation,

basically what i am trying to do is find out weighted average for meanterm by noofmats(ex 4 mats or 7 mats)

i have currency, countrym factorname and date column called IssueDate (out of it i have to get it year2008 , year2009, year 2010)

select currency,country, factorname, case datepart(yyyy,getdate()) when 2008 then Sum(convert(float, MeanTerm)) / count(Noofmats) as WeightedAverag else (0) end as Year2008,
case datepart(yyyy,getdate()) when 2009 then Sum(convert(float, MeanTerm)) / count(Noofmats) as WeightedAverag else (0) end as Year2009
case datepart(yyyy,getdate()) when 2010 then Sum(convert(float, MeanTerm)) / count(Noofmats) as WeightedAverag else (0) end as Year2010
group by currency,country,factorname

expect ouput results will be

Currency, country, FactorName, year2008, Year2009, Year2010
AUD, AUS, MidWire,35,46,37
AUD, AUS, Singale,11,16,3
AUD, NZ, MidWire,76,76,87
AUD, Singapore, MidWire,15,36,37
USD, CAN, Sizzle,315,14,67
USD, USA, CAPITAL,323,346,437

but i am unable to get what i desire is there anyway to get weighted average by year wise(which is not a column) it needs to be calculate in query iyself

please help me

thanks in advance
dhani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 04:51:40

can you explain whats formula you're trying to use ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/