title: Calculation in stored procedure (TSQL)
body: <p>I have to write  a calculation for the below scenario in the stored procedure .I have written the below code, Please let me know if it is correct or is there any other better way to write it.</p>
  
  <p>There is a NetWorth amount of some 'x' value and I need to calculate the commission for this 'x' value in the following conditions</p>
  
  <ol>
  <li>Total NetWorth up to £5,000 - 30%</li>
  <li>Total NetWorth up to £5,000.01 to £20,000 - 35%</li>
  <li>Total NetWorth up to £20,000.01 to £50,000 - 40%</li>
  <li>Total NetWorth up to £50,000.01 +  - 45%</li>
  </ol>
  
  <p>For example </p>
  
  <p>If the <code>NetWorth</code> is 100000, the calculation goes like this </p>
  
  <ol>
  <li>For the first 5000 of 100000 the commission is 30% i.e., 5000 * 0.30 = 1500  left out(95000)</li>
  <li>For the next 20000 of 95000 the commission is 35% i.e., 20000 * 0.35 = 7000 left out(75000)</li>
  <li>For the next 50000 of 75000 the commission is 40% i.e., 50000 * 0.40 = 20000 left out(25000)</li>
  <li>For the left out 25000 the commission is 45% i,e., 25000 * 0.45 = 11250</li>
  </ol>
  
  <p>and the total of all this commissions = point1 + point2 + point3 + point4 = 1500 + 7000 + 20000 + 11250 = 39750</p>
  
  <p>Below is the code in stored procedure I have written. Please let me know if this can be improved or there is any other way to write it.</p>
  
  <pre><code>DECLARE @NetWorth DECIMAL(18, 2) 
  DECLARE @InterMediateTier1Value DECIMAL(18, 2) 
  DECLARE @InterMediateTier2Value DECIMAL(18, 2) 
  DECLARE @InterMediateTier3Value DECIMAL(18, 2) 
  DECLARE @InterMediateTier1Commission DECIMAL(18, 2) 
  DECLARE @InterMediateTier2Commission DECIMAL(18, 2) 
  DECLARE @InterMediateTier3Commission DECIMAL(18, 2) 
  DECLARE @RemainderCommission DECIMAL(18, 2) 
  DECLARE @RemainderValue DECIMAL(18, 2) 
  
  SET @NetWorth = 40000 
  
  DECLARE @TotalCommission DECIMAL(18, 2) 
  
  IF @NetWorth &lt;= 5000 
    BEGIN 
        SET @InterMediateTier1Commission = @NetWorth * 0.30 
        SET @TotalCommission = @InterMediateTier1Commission 
    END 
  ELSE IF @NetWorth &gt; 5000 
     AND @NetWorth &lt;= 20000 
    BEGIN 
        SET @InterMediateTier2Value = @NetWorth - 5000 
        SET @InterMediateTier1Commission = 5000 * 0.30 
        SET @InterMediateTier2Commission = @InterMediateTier2Value * 0.35 
        SET @TotalCommission = @InterMediateTier1Commission 
                               + @InterMediateTier2Commission 
    END 
  ELSE IF @NetWorth &gt; 20000 
     AND @NetWorth &lt;= 50000 
    BEGIN 
        SET @InterMediateTier1Value = @NetWorth - 5000 
        SET @InterMediateTier1Commission = 5000 * 0.30 
  
        IF @InterMediateTier1Value &gt; 20000 
          SET @RemainderValue = @InterMediateTier1Value - 20000 
  
        SET @RemainderCommission = @RemainderValue * 0.40 
        SET @InterMediateTier2Commission = 20000 * 0.35 
        SET @TotalCommission = @InterMediateTier1Commission 
                               + @InterMediateTier2Commission 
                               + @RemainderCommission 
    END 
  ELSE IF @NetWorth &gt; 50000 
    BEGIN 
        SET @InterMediateTier1Value = @NetWorth - 5000 
        SET @InterMediateTier1Commission = 5000 * 0.30 
  
        IF @InterMediateTier1Value &gt; 20000 
          SET @RemainderValue = @InterMediateTier1Value - 20000 
  
        SET @InterMediateTier2Commission = 20000 * 0.35 
  
        IF @RemainderValue &gt; 50000 
          SET @InterMediateTier4Value = @RemainderValue - 50000 
  
        SET @InterMediateTier3Commission = 50000 * 0.40 
        SET @RemainderCommission = @RemainderValue * 0.45 
        SET @TotalCommission = @InterMediateTier1Commission 
                               + @InterMediateTier2Commission 
                               + @InterMediateTier3Commission 
                               + @RemainderCommission 
    END 
  
  SELECT @TotalCommission AS TotalCommission 
  </code></pre>