title: SQL avoiding Multipart Identifier on subquery
body: <p>Using SQLExpress 2017</p>
  
  <p>I have some products distributed over warehouses and I would like to see how many items would need to be stocked to meet the sales of a given period.</p>
  
  <pre><code>Need pr. warehouse = Stock - CustomerOrders + SupplierOrders - SumOfSalesInPeriod
  </code></pre>
  
  <p>Now I would like to sum that up for each product, but I am no interested in warehouses where the need is already met, so I only want the negative values but I have some trouble getting it to work as I am met with Multipart Identifier errors.
  Using the distinct keyword also makes me think that I am doing too many calculations and there must be a better way of doing this.</p>
  
  <pre><code>declare @fromDate date = '1900-01-01 12:00:00';
  declare @toDate date = '3000-01-01 12:00:00';
  
  select *,
      balance =  
          (select 
              turn = sum(TurnOver)
          from (
          select 
              WarehouseStocks.Id,
              TurnOver = WarehouseStocks.Qty 
                          - WarehouseStocks.OrderedByCustomersQty 
                          + WarehouseStocks.OrderedFromSuppliersQty 
                          - isnull((select Sum(StockEntries.Qty) 
                                      from StockEntries 
                                      where 
                                          StockEntries.Type = 1 
                                          and StockEntries.ProductId = WarehouseStocks.Id 
                                          and WarehouseStocks.WarehouseId = StockEntries.WarehouseId 
                                          and StockEntries.Date &gt;= @fromDate 
                                          and StockEntries.Date &lt;= @toDate), 0) 
          from WarehouseStocks) Product where TurnOver &lt; 0
          group by Product.Id) tp where Products.Id = tp.Id)
  from Products
  </code></pre>