SQL Server : calculated column for parent/child level
<p>Having some issues displaying a calculated column. </p>

<p>I'm displaying a line for parent, then a few (anywhere between 1-5) lines for each child item. </p>

<p>My first query is this:</p>

<pre><code>Select 
    _r1 = pbhier.r1 
    , pb.parent_id
    , pb.Product_id
    , pb.LastCost 
    , pb.AverageCost 
    , pb.FirstDatePurchased 
    , lrd.LastReceivingDate 
    , inv_units = isnull(inv.quantity,0)
    , sales_units = convert(int,isnull(sales.SalesUnits,0)) 
    , number_months_supply = case when isnull(sales.salesUnits,0) = 0 then isnull(inv.quantity,0) 
                                  else 
                                    case when (isnull(sales.salesUnits,0)/12) = 0 then 0 
                                         else 
                                            (convert(int,(inv.quantity/(isnull(sales.salesUnits,0)/12)))) end end
    , inv.quantity, sales.SalesUnits
    , ext_AverageCost = isnull(inv.quantity,0) * pb.averagecost 
INTO #tempdata
From Products pb 
    inner join #TempFinalHier pbhier on pb.product_id = pbhier.product_id 
    left join #inventoryPC inv (nolock) on pb.product_id = inv.product_id 
    left join #tempTotSalesPC sales (nolock) on pb.product_id = sales.product_id 
    left join #lastReceivingDate lrd on lrd.rm_product_id = pb.product_id 
where pb.Parent_ID in (select parent_id from #FinalParentSelection)
</code></pre>

<p>This creates a temp data set. The column "number_months_supply" does not need to be calculated above but the same formula should be used in my final query. My final query below gets me the final result set:</p>

<pre><code>    Select distinct
        H.R1 , H.R2, H.Lvl,
        case 
           when R1 &lt;&gt; R2 
              then H.parent_sku 
              else H.SKU 
        end as SKU, 
        H.parent_sku,
        FirstReceiptDate = min(B.FirstDatePurchased),
        LastReceiptDate = max(B.LastReceivingDate), 
        OnHand = sum(B.inv_units),
        SalesUnits = sum(B.sales_units), 
        case 
           when isnull(sum(B.sales_units),0) = 0 
              then isnull(sum(B.inv_units),0) 
              else 
                 case 
                    when (isnull(sum(B.sales_units),0)/12) = 0 
                    then 0 
                    else convert(decimal(10, 2), (sum(B.inv_units) / (isnull(sum(B.sales_units), 0) / 12)))  
                 end 
        end,
        max(B.ext_AverageCost) as ext_AverageCost
    From 
        #TempFinalHier H
    join 
        #TempData1 B on (B._R1 between H.R1 and H.R2)
--  where H.Lvl=1       --Show only parent line
    Group By
        H.R1, H.R2, H.Lvl, H.parent_sku
    having 
        sum(B.number_months_supply) &gt; 12    --@MonthsSupplyGreaterThanOrEqual
    Order by    
        R1
</code></pre>

<p>What I'm trying to get is:
'onHand' for parent line (lvl1) needs to be sum of all child rows (lvl2). same applies to 'SalesUnits'. Finally, Number_months_supply formula is (inv/sales_units)*12. I would like to display the number_months_supply as numeric with 2 decimal points. </p>

<p>Would appreciate some help with this.</p>

<p>Thanks. </p>