title: How to make a recursive query dynamically?
body: <p>How to make a recursive query dynamically ?</p>
  
  <p>Let's say that :</p>
  
  <p><code>QuantityDate</code> may be :</p>
  
  <pre><code>Month 3  The first quarter 
  Month 6  The second quarter
  Month 9  The third quarter
  Month 12 The fourth quarter
  </code></pre>
  
  <p>If the user select the second quarter <code>6</code></p>
  
  <blockquote>
    <p>Then I will sum the data in first quarter + the data in second quarter</p>
  </blockquote>
  
  <p>If the user select the third quarter <code>9</code></p>
  
  <blockquote>
    <p>Then I will sum the data in first quarter + the data in second quarter
    + the data in the third quarter</p>
  </blockquote>
  
  <p>etc ...</p>
  
  <p><strong>My Query is like this :</strong></p>
  
  <p>Here the user select the second quarter <code>(6)</code>, So I sum first Quarter data + second Quarter data like this:</p>
  
  <pre><code>,((ISNULL(d.SalesCost,0)*-1)/
  
  (ISNULL(d.LocalSales,0)+ISNULL(d.Exporting,0) --The selected quarter data
  +
  (SELECT ISNULL(e.LocalSales,0)+ISNULL(e.Exporting,0)--The First Quarter
  FROM CTE e
  WHERE e.[Year] = d.[Year] 
  AND e.period =2
  AND e.PeriodTypeId =2 
  AND e.CompanyId = d.CompanyId
  AND MONTH(e.QuantityDate)=3))) AS Tot1,
  ,d.QuantityDate
  FROM CTE d 
  INNER JOIN CTE t 
  ON d.[Year] = t.[Year] 
  AND d.period = t.period
  AND d.PeriodTypeId = t.PeriodTypeId
  AND d.CompanyId = t.CompanyId
  AND d.QuantityDate =t.QuantityDate
  WHERE  d.[Year] IN (2016,2015) AND d.PeriodTypeId =2 AND d.Period =2
  AND YEAR(d.QuantityDate)IN (2016,2015) AND MONTH(d.QuantityDate)=6
  ORDER BY d.CompanyId, d.[Year] DESC
  </code></pre>
  
  <hr>
  
  <p>My Whole query:</p>
  
  <p><a href="http://pastebin.com/Yr2vzYe9" rel="nofollow noreferrer">Query</a></p>
  
  <hr>
  
  <p>Sample Data :</p>
  
  <p>Let's say Quantity Table like this :</p>
  
  <pre><code>LocalSales  Exporting  QuantityDate
   15           20       1-3-2016
   22           80       1-6-2016
   40           20       1-9-2016
  </code></pre>
  
  <p>When the user select the second quarter i get both the first and the second quarter data in <code>CTE d</code></p>
  
  <pre><code> 15           20       1-3-2016
   22           80       1-6-2016
  </code></pre>
  
  <p>Then i Join it with <code>CTE e</code></p>
  
  <p>And If say that <code>SalesCost</code> = <code>-20</code> for example</p>
  
  <p>Then <code>tot1</code> should  = <code>20/((15+20)+(22+80))</code></p>