Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Weighted Distibution/Bell-Curve Calculation + Query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, September 6, 2009 5:35 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, April 6, 2010 11:10 PM Points: 5, Visits: 44
 I have been asked to create a weighted distribution calcuation for a SRS report for the Business Development department. I have never done anything like this using T-SQL and really could use some advice. The issue I am having is figuring out how to code this thing so that when the query runs it knows what data needs to be calculated when and with what percentage (margin * margin pct). Being a bell curve calculation over an equal distribution calc the margin % will vary depending on when the project starts and how long the term is. Example... you have a project that totals \$1M in revenue with a \$250,000 margin and it is 6 months long. The project say is already a month under way and the bell curve calc for that range is m1 (10%), m2 (15%), m3 (15%), m4 (25%), m5 (25%), m6 (10%). The output should appear something like this:Project | Description | Revenue | Margin | Sept 2009 | Oct 2009 | Nov 2009 | Dec 2009 | Jan 2010 | Feb 2010 |Project Name | DESC | \$1,000,000 | \$250,000 | \$37,500 | \$37,500 | \$62,500 | \$62,500 | \$25,000 | \$0Notice how the first month which is forecasted at 10% in the bell curve calc doesnt appear, that is because the project is already a month in so therefore that month doesnt appear on the current forecast report. It has to begin its calc in month 2. That is also why 6 months out (Feb 2010) is 0 because the project will be complete then.Anyone have a clue how to do this?
Post #783560
 Posted Sunday, September 6, 2009 8:13 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, February 9, 2010 2:41 AM Points: 140, Visits: 1,019
 I didn't get what is the function that gives the % value for each month...Cuold not see the connection between the numbers (10,15,25%,...)
Post #783583
 Posted Sunday, September 6, 2009 10:33 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, April 6, 2010 11:10 PM Points: 5, Visits: 44
 Thanks for your reply. It's an example of what the bell curve calculations are. Obviously equal distribution would be a matter of dividing the margin equally over the course of the term (i.e. \$250K / 9)... With the bell curve calculation its different in that the calculations for each month vary... 10% of margin one month followed by say 20% the following and so on....I havent ever written anything like this before and havent seen any examples anywhere on the net. I was thinking maybe a stored procedure or CURSOR may be an option but was hoping someone on here has coded something like this before and could offer a suggestion.
Post #783599
 Posted Monday, September 7, 2009 10:06 AM
 Hall of Fame Group: General Forum Members Last Login: Tuesday, November 29, 2016 2:42 PM Points: 3,370, Visits: 6,889
 I will suggest that you give us an example using sample tables and data, to accompany your picture of what the expected output should be. Simple text explanations in the absence of source data lead to long sessions of question-and-answer that can be often be shortened dramatically with just a little effort to create some data.For examples of how to set up a problem to get tested solutions faster, read this article.Thanks __________________________________________________Against stupidity the gods themselves contend in vain. -- Friedrich Schiller Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Post #783849
 Posted Monday, September 7, 2009 11:39 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, April 6, 2010 11:10 PM Points: 5, Visits: 44
Post #783872
 Posted Tuesday, September 8, 2009 6:49 AM
 Ten Centuries Group: General Forum Members Last Login: Friday, January 30, 2015 8:42 AM Points: 1,332, Visits: 19,324
 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 , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL UNION ALL SELECT 4 , .15 , .3 , .3 , .25 , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL UNION ALL SELECT 5 , .12 , .25 , .25 , .23 , .15 , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL UNION ALL SELECT 6 , .1 , .2 , .2 , .2 , .2 , .1 , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL UNION ALL SELECT 18 , .03 , .03 , .03 , .03 , .05 , .07 , .08 , .09 , .08 , .08 , .08 , .08 , .07 , .05 , .05 , .05 , .03 , .02SELECT *FROM #bellcurveDECLARE @budget MONEY , @startdate DATETIME , @enddate DATETIME , @terms INT , @sql VARCHAR(2000) , @i INT -- loop counterSET @budget = 1000000SET @startdate = '1/1/2009' SET @enddate = '7/1/2010'SET @terms = DATEDIFF(mm, @startdate, @enddate)SET @i = 1SET @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 ENDSET @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 questionHow to post performance problemsTally Table:What it is and how it replaces a loop"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."
Post #784193
 Posted Tuesday, September 8, 2009 7:59 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, April 6, 2010 11:10 PM Points: 5, Visits: 44
 Cool! That does help! I figured the same thing last night so I already created the table... I just seemed to be having an issue with the start dates and the calculations happening when they are supposed to. I will go through your example again and see if that can help me fix the issue!I'll let you know if that does it!Thanks again!!
Post #784263

 Permissions