Is this the correct way to create a Weighted Avg?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Is this the correct way to create a Weighted Avg??

Author

Topic

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2012-07-10 : 00:56:56

Here's the initial query that I needed to make certain fields a weighted average based on the field KLA as the weight. KLA is a varchar field.

 
	select  eer.Market,    
		eer.KLA,
		eer.Term,
		case  scdesc when 'ics' then 'Act' else eer.ScDesc end as ScDesc,   
		eer.Term As Term_Anual,    
		eer.VaMonths,    
    		eer.RentPrice,    
    		eer.BuyPrice,          
		eer.TransactionID
    from View1 eer
    WHERE ((@Term = 'Long Term' and Term_Anual > 11)    
      OR (@Term = 'Short Term' and Term_Anual < 12))    
      and (ArchiveID in (@ArchiveID))
    order by eer.market,
    	 eer.KLA,
    	 eer.ScDesc

This is my first attempt ever to do a weighted average so there are a few things I'm not sure of.

So what I did is try to create a CTE by using a join.

 
    SELECT	[base].Market,
    	[base].MSA,
    	[base].ArchiveID,
    	[base].TransactionID
    	[base].ScDesc,
    	SUM([base].KLA) * [weighted].KLA / SUM([weighted].KLA) as KLA,
    	SUM([base].VaMonths) * [weighted].VaMonths / SUM([weighted].VaMonths) as VaMonths,
    	SUM([base].RentPrice) * [weighted].RentPrice / SUM([weighted].RentPrice) as RentPrice,
    	SUM([base].BuyPrice) * [weighted].BuyPrice / SUM([weighted].BuyPrice) as BuyPrice		
    FROM View1 [base]
    INNER JOIN
    (
    	select  eer.Market,    
    		eer.KLA,
    		eer.Term,
    		case  scdesc when 'ics' then 'Act' else eer.ScDesc end as ScDesc,   
    		eer.Term As Term_Anual,    
    		eer.VaMonths,    
        		eer.RentPrice,    
        		eer.BuyPrice,          
    		eer.TransactionID
    from View2 eer
    WHERE((@Term = 'Long Term' and Term_Anual > 11)    
      OR (@Term = 'Short Term' and Term_Anual < 12))    
      and (ArchiveID in (@ArchiveID))
    order by eer.market,
    	 eer.KLA,
    	 eer.ScDesc
    ) as [weighted]

Also, I don't know where I should or if I should group by KLA in the two queries I guess to get the weight either.

So does this look right in terms of trying to make some of the fields (the ones with SUM) weighted?

I need some hand holding first time through as I'm not confident I am sure I'm doing this right. The sums are being done in fields that are money, numeric, etc. type of fields so don't worry about if they're a proper field type. The rest of the fields that are [base]. are not weighted, I don't want those weighted obviously so selecting them straight up in my outer query.

Side Note: Man this editor sucks ! :P They need to upgrade and refactor this forum, the tools are not adequate...put some jquery editor or TinyMCE SQLTeam.com!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources