RE: Weighted Distibution/Bell-Curve Calculation + Query

SSC-Insane

Points: 24198

September 8, 2009 at 6:49 am

So you're forcing your project projection to show the calculated values based on the percentages provided? You're not calculating the curve based on actual values?

If that's the case, and your distribution will never change, just build a table with your percentages for each possible project length. Then when you need to show the calculation, take the budget, determine how many terms it has, what term you're in, and display the result.

I'll work on a sample in a few...

[editing to add sample]

Someone may show you how to do this cleaner, but since you have a finite distribution, I just used a loop. Change your Startdate, Enddate, budget as required, only displays the months used with the final dollars applied.

CREATE TABLE #bellcurve

(

term INT ,

month1 DECIMAL(2, 2) ,

month2 DECIMAL(2, 2) ,

month3 DECIMAL(2, 2) ,

month4 DECIMAL(2, 2) ,

month5 DECIMAL(2, 2) ,

month6 DECIMAL(2, 2) ,

month7 DECIMAL(2, 2) ,

month8 DECIMAL(2, 2) ,

month9 DECIMAL(2, 2) ,

month10 DECIMAL(2, 2) ,

month11 DECIMAL(2, 2) ,

month12 DECIMAL(2, 2) ,

month13 DECIMAL(2, 2) ,

month14 DECIMAL(2, 2) ,

month15 DECIMAL(2, 2) ,

month16 DECIMAL(2, 2) ,

month17 DECIMAL(2, 2) ,

month18 DECIMAL(2, 2)

)

INSERT INTO #bellcurve

SELECT 3 ,

.2 ,

.5 ,

.3 ,

NULL ,

NULL

UNION ALL

SELECT 4 ,

.15 ,

.3 ,

.25 ,

NULL ,

NULL

UNION ALL

SELECT 5 ,

.12 ,

.25 ,

.23 ,

.15 ,

NULL ,

NULL

UNION ALL

SELECT 6 ,

.1 ,

.2 ,

.1 ,

NULL ,

NULL

UNION ALL

SELECT 18 ,

.03 ,

.05 ,

.07 ,

.08 ,

.09 ,

.08 ,

.07 ,

.05 ,

.03 ,

.02

SELECT *

FROM #bellcurve

DECLARE @budget MONEY ,

@startdate DATETIME ,

@enddate DATETIME ,

@terms INT ,

@sql VARCHAR(2000) ,

@i INT

-- loop counter

SET @budget = 1000000

SET @startdate = '1/1/2009'

SET @enddate = '7/1/2010'

SET @terms = DATEDIFF(mm, @startdate, @enddate)

SET @i = 1

SET @sql = 'SELECT '

SELECT 'Budget = $' + CONVERT(VARCHAR, @budget) + CHAR(13)

+ 'Project starting on: ' + CONVERT(VARCHAR, @startdate, 101)

+ CHAR(13) + 'Project ending on: ' + CONVERT(VARCHAR, @enddate, 101)

+ CHAR(13) + 'Project has ' + CONVERT(VARCHAR, @terms) + ' terms.'

WHILE @i <= @terms

BEGIN

SET @sql = @sql + CONVERT(VARCHAR, @budget) + ' * month'

+ CONVERT(VARCHAR, @i) + ' AS ' + LEFT(DATENAME(mm,

DATEADD(mm, @i - 1,

@startdate)), 3)

+ DATENAME(yyyy, DATEADD(mm, @i - 1, @startdate)) + ','

SET @i = @i + 1

--PRINT @sql

END

SET @sql = LEFT(@sql, LEN(@sql) - 1) + '

FROM #bellcurve AS b

WHERE b.term = ' + CONVERT(VARCHAR, @terms)

--PRINT (@sql)

EXEC(@sql)

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."