Need to calculate a weighted average using sql

Help!!! I'm in a time crunch and need to calculate a weighted average.
Example: 85% of players will win $10.00, 5% will win $100. I have a table
that is storing the counter information. I just drawing a blank!!!
Any help is appreciated!!!JMainus,
Can you provide some DDL, sample data and expected result?
AMB
"JMainus" wrote:

> Help!!! I'm in a time crunch and need to calculate a weighted average.
> Example: 85% of players will win $10.00, 5% will win $100. I have a tabl
e
> that is storing the counter information. I just drawing a blank!!!
> Any help is appreciated!!!|||Alejandro,
I have a table that is storing the amounts available; $10, $20, $50 and
also the number of available awards per amount, and the percentage field
stores the weighted average; ex. 85% can win $10.
Thanks for your help.
"Alejandro Mesa" wrote:
> JMainus,
> Can you provide some DDL, sample data and expected result?
>
> AMB
> "JMainus" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

need MDX help on calculated weighted average based on time

I am working on a project using financial data (chart of accounts) and I need to create a calculated member(measure).

The measure is the dollar amount for the balance sheet accounts as they roll up over the time dimension, they need to be weighted by the number of days in the period. So instead of it being a normal average it should be weighted by the days in the period.

I have Number of days in Month, Quarter, and Year as attributes in the Time Dimension([DaysInMonth], [DaysInQuarter], [DaysInYear])

Here is an example:

Qtr 1 = (Jan. balance x 31)+(Feb bal x 28)+(mar bal x 31)/total number of days in the period (90 in this example)

My Measure Name is Amount and My time Dimension/Heirarchy is [DimTime].[Calendar Time] respectively.

Has anyone created a similar measure? Can you show me the MDX? This is a tight timeline and any help would be GREATLY appreciated!

DRR

While I'm certain you can pull this query off with MDX, I wonder if you might be better served by storing the component values as measures. Think about it this way, no matter where you are in the cube, you will always have to pull balance data from the lowest level, multiply it by a number specific to that month, and then roll up both the number of days in the months affected and the weighted balance before then doing a division operation. I think you might run into performance problems.

So instead, I'd suggest creating two measures. One is DaysInReportingPeriod. The other is WeightedBalance which is your balance times days. Create these in your relational data warehouse or in the DSV. Then, add the measures to the cube with aggregation set to SUM and hide them (Set Visible=False). Then, all you have to do is create a calculation that divides [Measures].[WeightedBalance] by [Measures].[DaysInReportingPeriod].

Good luck,
Bryan