Log in  ::  Register  ::  Not logged in

## Weighted Distibution/Bell-Curve Calculation + Query

 Author Message nixaler SSC-Enthusiastic Group: General Forum Members Points: 183 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? dbo.benyos SSC Eights! Group: General Forum Members Points: 968 Visits: 1019 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%,...) Tal Ben Yosefwww.TalBenYosef.com(visit my LinkedIn profile) nixaler SSC-Enthusiastic Group: General Forum Members Points: 183 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. The Dixie Flatline One Orange Chip Group: General Forum Members Points: 28900 Visits: 6935 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 nixaler SSC-Enthusiastic Group: General Forum Members Points: 183 Visits: 44 Bob - Thank you as well for your reply... I apologize for not explaining this as well as possible Here is a small example of the coding used for the equal monthly distribution I was reffering to in my original post: (I shortened it to only show you two months of calculation obviously to save space here. So the completed version goes out 24 months, not 2.)SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE usp_WeightedDistCalc WITH ENCRYPTION ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT OpportunityBase.Name, OpportunityBase.Description, SystemUserBase.LastName, SystemUserBase.FirstName, OpportunityBase.CloseProbability, OpportunityBase.EstimatedValue, OpportunityExtensionBase.New_Margin, OpportunityExtensionBase.New_ConstructionStartDate, OpportunityExtensionBase.New_OpportunityReport, OpportunityExtensionBase.New_ConstructionTerm, OpportunityExtensionBase.New_RFP, DATEADD(MONTH,+1,(DATEADD(MONTH,OpportunityExtensionBase.New_ConstructionTerm, OpportunityExtensionBase.New_ConstructionStartDate))) - DAY(DATEADD(MONTH,OpportunityExtensionBase.New_ConstructionTerm, OpportunityExtensionBase.New_ConstructionStartDate))AS EndDate, Month1 = case when GetDate() between OpportunityExtensionBase.New_ConstructionStartDate and DAteadd(month,+1,(DateAdd(month,OpportunityExtensionBase.New_ConstructionTerm, OpportunityExtensionBase.New_ConstructionStartDate))) - day(DateAdd(month,OpportunityExtensionBase.New_ConstructionTerm, OpportunityExtensionBase.New_ConstructionStartDate)) then (case ISNULL(New_ConstructionTerm, 0) when 0 then 0 else ((ISNULL(New_Margin, 0)) / (case ISNULL(New_ConstructionTerm, 1) when 0 then 1 else ISNULL(New_ConstructionTerm, 1) end)) end) else 0 end, Month2 = case when dateadd(month,+1,GetDate()) between OpportunityExtensionBase.New_ConstructionStartDate and DAteadd(month,+1,(DateAdd(month,OpportunityExtensionBase.New_ConstructionTerm, OpportunityExtensionBase.New_ConstructionStartDate))) - day(DateAdd(month,OpportunityExtensionBase.New_ConstructionTerm, OpportunityExtensionBase.New_ConstructionStartDate)) then (case ISNULL(New_ConstructionTerm, 0) when 0 then 0 else ((ISNULL(New_Margin, 0)) / (case ISNULL(New_ConstructionTerm, 1) when 0 then 1 else ISNULL(New_ConstructionTerm, 1) end)) end) else 0 end, FROM OpportunityBase INNER JOIN OpportunityExtensionBase ON OpportunityBase.OpportunityId = OpportunityExtensionBase.OpportunityId INNER JOIN SystemUserBase ON OpportunityBase.OwningUser = SystemUserBase.SystemUserIdENDGO________________________________________________________________As you can see the calculation for the above query is a simple division of (Margin / Term). Also creating the months out I need to go (24) by using the DATEADD function. I need to achive pretty much the same layout except now instead of equally diving the margin by the term (in months) I need to have the calculations vary based off of the bell curve calculation provided. See below:So - for example, if you look at the chart above. Say a project term is 9 months, the monthly calculations have to be as the chart above states... I hope this clears up some of the mess jcrawf02 SSChampion Group: General Forum Members Points: 10962 Visits: 19324 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." nixaler SSC-Enthusiastic Group: General Forum Members Points: 183 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!!

## Permissions

 You can't post new topics. You can't post topic replies. You can't post new polls. You can't post replies to polls. You can't edit your own topics. You can't delete your own topics. You can't edit other topics. You can't delete other topics. You can't edit your own posts. You can't edit other posts. You can't delete your own posts. You can't delete other posts. You can't post events. You can't edit your own events. You can't edit other events. You can't delete your own events. You can't delete other events. You can't send private messages. You can't send emails. You can read topics. You can't vote in polls. You can't upload attachments. You can download attachments. You can't post HTML code. You can't edit HTML code. You can't post IFCode. You can't post JavaScript. You can post emoticons. You can't post or upload images.

##### Select a forum
 SQL Server 2017      SQL Server 2017 - Administration      SQL Server 2017 - Development SQL Server 2016      SQL Server 2016 - Administration      SQL Server 2016 - Development and T-SQL SQL Server 2014      Administration - SQL Server 2014      Development - SQL Server 2014 SQL Server 2012      SQL 2012 - General      SQL Server 2012 - T-SQL SQL Server vNext      SQL Server 15 - Administration      SQL Server 15 - Development SQL Server 2008      SQL Server 2008 - General      T-SQL (SS2K8)      June 2007 CTP      Working with Oracle      July CTP      SQL Server Newbies      Security (SS2K8)      SQL Server 2008 High Availability      SQL Server 2008 Administration      Data Corruption (SS2K8 / SS2K8 R2)      SQL Server 2008 Performance Tuning Cloud Computing      SQL Azure - Development      SQL Azure - Administration      Amazon AWS and other cloud vendors      General Cloud Computing Questions      CosmosDB      Azure Data Lake      Azure Machine Learning Reporting Services      Reporting Services      Reporting Services 2005 Administration      Reporting Services 2005 Development      Reporting Services 2008/R2 Administration      Reporting Services 2008 Development      SSRS 2012      SSRS 2014      SSRS 2016 Programming      Connecting      General      SMO/RMO/DMO      XML      Service Broker      Powershell      Testing      TFS/Data Dude/DBPro      SSDT      Continuous Integration, Deployment, and Delivery      R Services and R Language Data Warehousing      Integration Services      Strategies and Ideas      Analysis Services      Data Transformation Services (DTS)      Performance Point      Data Mining      PowerPivot      R language      Machine Learning Database Design      Disaster Recovery      Design Ideas and Questions      Relational Theory      Hardware      Virtualization      Security and Auditing SQLServerCentral.com      Anything that is NOT about SQL!      Contests!      Editorials      SQLServerCentral.com Announcements      SQLServerCentral.com Website Issues      Suggestions      Tag Issues with Content      Podcast Feedback      SQLServerCentral.com Test Forum      Articles Requested SQL Server 2005      Administering      Backups      Business Intelligence      CLR Integration and Programming.      Data Corruption      Development      Working with Oracle      SQL Server 2005 Compact Edition      SQL Server 2005 General Discussion      SQL Server 2005 Security      SQL Server 2005 Strategies      SS2K5 Replication      SQL Server Express      SQL Server 2005 Performance Tuning      SQL Server 2005 Integration Services      T-SQL (SS2K5)      SQL Server Newbies SQL Server 7,2000      Administration      Backups      Data Corruption      General      Globalization      In The Enterprise      Working with Oracle      Security      Strategies      SQL Server Newbies      Service Packs      SQL Server CE      Performance Tuning      Replication      Sarbanes-Oxley      T-SQL      SQL Server Agent SQL Server and other platforms      MySQL      Oracle      PostgreSQL      DB2      SQL Server and Sharepoint Older Versions of SQL (v6.5, v6.0, v4.2)      Older Versions of SQL (v6.5, v6.0, v4.2) Career      Certification      Employers and Employees      Events      Job Postings      Resumes and Job Hunters      Presentations and Speaking      Retired Members Testing Center      SQL Server Security Skills      Question of the Day (QOD) Microsoft Access      Microsoft Access Products and Books      Third Party Products         SQLCentric         Extreme Technologies.         Innovartis         Embarcadero         SQL Sentry         Sonasoft         Golden Gate Software         Idera         Lumigent         Red Gate Software         ApexSQL         Quest Software         CA      Discussions about Books         Discuss Programming Books          Discuss XML Books          Discuss T-SQL Books          Discuss Data Warehousing Books          Discuss DTS Books          Discuss SQL Server 2000 Books         Discuss SQL Server 7.0 Books Notification Services      Administration Article Discussions Future Versions      SQL 12

## Search

 Copyright © 2002-2018 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.