title: SQL Server 2012 Adding/Subtracting Values in Hierarchy with Function
body: <p>I have a Problem evaluating my hierarchial data and would be very pleased, if any of you had a tip.</p>
  
  <p>My table has 4 columns: <code>ID</code> is self explanatory, <code>parentID</code> creates the hierarchy and there is only one parent for each child, <code>value</code> is just a numeric value of currency (some rows will have NULL as their Value) and finally <code>operator</code> which is a bit that can be 3 Values <code>0, 1 and NULL</code> (I want to it correspond to <code>-, + and ignore</code> but more about that later). Operator directly discloses the operator with which the child is related to its parent.</p>
  
  <p>E.g. <code>ID, ParentID, Operator, Value</code> <code>23, 2, 1, 2.22</code> has the operator 1 and thus shall be added when trying to calculate the value of its parent with<code>ID = 2</code></p>
  
  <p>The purpose of my function will be to calculate the <code>value</code> of a given <code>ID</code> by recursively checking for all the children and if the <code>operator</code> is <code>1</code> <em>adding</em> or if it is <code>0</code> <em>subtracting</em> the value from the total (if it is <code>NULL</code> the value will be ignored).</p>
  
  <p>So far I had written a function with a recursive <strong>cte</strong> that finds all children and sub-children of a given <code>ID</code> gathers them together in one table and then takes the sum after applying a <code>case</code>.</p>
  
  <pre><code>function [dbo].[total](
  @id int)
  returns NUMERIC(20,2)
  AS
  BEGIN
  declare @total numeric(20,2);
  
  cte as(
  SELECT id, parentid, operator, value, 0 as level
      FROM data
      WHERE pos = @pos
      union all
   select t.id, t.parentid, t.operator, t.value, c.level + 1
   FROM
      data t
   inner join cte c on c.id = t.parentid)
  
   SELECT @total = (select sum (case when operator = 0 then (5 * value) when operator = 1 then value/100 else 0 end) from cte);
  RETURN @total
  END
  </code></pre>
  
  <p>This function works to some extend e.g. <code>dbo.total(1)</code> would give me the correct result of 1 <code>-2.5+3.5</code> in this example.</p>
  
  <pre><code>ID       Operator  Value  ParentID
  1        1         NULL    -
   2       1         NULL    1
    3      0         2.5     2
    4      1         3.5     2
  </code></pre>
  
  <p>In other cases however this doesn't work as well. In this example it would return a value of <code>5 + 2.5 + 2.5 = 10</code> even though I would want it to "recognize" that the parent of <code>3</code> and <code>4</code> (<code>2</code>) has an operator of 0 and thus their values should be subtracted, resulting in a different calculation <code>5 - (2.5 + 2.5) = 0</code></p>
  
  <pre><code>ID       Operator  Value  ParentID
  1        1         5    -
   2       0         NULL    1
    3      1         2.5     2
    4      1         2.5     2
  </code></pre>
  
  <p>I hope i am not the first one facing this problem and am looking forward to any answers that you might have on this topic. Should you have questions, feel free to ask me. :)
  Thanks a lot!</p>