2

I'm totally new to doing calculations in T-SQL. I guess I'm wondering what is a weighted average and how do you do it in T-SQL for a field?

First off as far as I know a weighted average is simply just multiplying 2 columns then average it by dividing by something.

Here's an example of a calculated field I have in my view, after calling one of our UDFs. Now this field in my view needs to also be a weighted average....no idea where to start to turn this into a weighted average.

So ultimately this UDF returns the AverageCostG. I call the UDF from my view and so here's the guts of the UDF:

Set @AverageCostG = ((@AvgFullYear_Rent * @Months) +
                            (@PrevYearRent * @PrevYearMonths))
                            / @Term

so in my view I'm calling the UDF above to get back that @AverageCostG

CREATE View MyTestView 
AS   
select v.*, --TODO: get rid of *, that's just for testing, select actual field names
        CalculateAvgRentG(d.GrossNet, d.BaseMonthlyRent, d.ILI, d.Increase, d.Term) as AverageRent_G,
....

from SomeOtherView v

Now I need to make this AverageRent_G calc field in my view also a weighted average somehow...

Do I need to know WHAT they want weighted or is it assumed that hey, it's obvious.. I do not know what I need to know in order to do the weighted average for these guys...like what specs I need if any from them other than this calculation I've created based off the UDF call. I mean do I have to do some crazy select join or something in addition to multiplying 2 fields and dividing by something to average it? How do I know what fields they are to be used int he weighted average and from where? I will openly admit I'm totally new to BI T-SQL development as I'm an ASP.NET MVC C#/Architect dev...and lost to this calculation stuff in T-SQL.

I have tried to research this but just need some basic hand holding the first time through this, my head hurts right now caue I don't know what info I need to obtain from them and then what to do exactly to make that calc field weighted.

PositiveGuy
  • 46,620
  • 110
  • 305
  • 471
  • I probably made the question longer than it should be and probably repeated the same crap a few times but my head is spinning so forgive me... – PositiveGuy Jun 28 '12 at 05:45
  • 1
    Your client should be able to tell you how they want the data weighted as you can weight any given data in a virtually unlimited number of ways. An example of this might be that they want to weight by an inflation factor to get the 'real' income for this and the prior year, but without specific requirements you can't begin to weight meaningfully! – Dibstar Jun 29 '12 at 09:18

1 Answers1

0

They'll have to tell you what the weighting factor is. This is my guess.

SUM([weight] * CalculateAvgRentG(...)) / SUM([weight])
shawnt00
  • 16,443
  • 3
  • 17
  • 22