title: Restricting WHERE clause to specific SELECT statement in sql
body: <p>I have the following sql code. I use a WHERE clause with a date interval. My problem is that I only want the date interval to be used on a specific part of the select statement.</p>
  
  <p>I want to restrict the WHERE clause DATE to my select on QtyInPeriod and ProfitInPeriod and not the other columns selected (Lines.Item, Inventory.ItemAlternative, Inventory.OnHandPhys, Inventory.Allocated, Inventorry.CostPrice, Brand &amp; StockStatus).</p>
  
  <p>Do I need to fetch the data in 2 seperate sql querys or can I limit my WHERE DATE clause to only a part of the select statement?</p>
  
  <pre><code>SELECT lines.Item,
         Inventory.ItemAlternative,
         Inventory.OnHandPhys,
         Inventory.Allocated,
         Inventory.CostPrice,
         SUM(lines.Invoiced)*-1 AS QtySoldInPeriod,
         (SUM(lines.AmountBase-lines.CostAmount))*-1 AS ProfitInPeriod,
         replace(vInventoryOptional2Values.Value, '/', '') AS Brand,
         Inventory.Optional3 AS StockStatus
  FROM lines
  INNER JOIN inventory ON lines.item = Inventory.item
  INNER JOIN vInventoryOptional2Values ON Inventory.Optional2 = vInventoryOptional2Values.RecordID
  WHERE Inventory.Item = 'ITEM#'
    AND Lines.Date &gt;= 'DATE'
    AND Lines.Date &lt;= 'DATE'
    AND lines.Invoiced &lt; 0
    AND Inventory.Status = 0
    AND Inventory.Optional3 &lt;&gt; 3
  GROUP BY Lines.Item,
           Inventory.Optional3,
           Inventory.ItemAlternative,
           Inventory.OnHandPhys,
           Inventory.CostPrice,
           vInventoryOptional2Values.Value,
           Inventory.Allocated
  HAVING SUM(lines.AmountBase-lines.CostAmount) &lt; 0
  </code></pre>
  
  <p>EDIT:
  I have tried with a sub-query, but can't get it to work:</p>
  
  <pre><code>SELECT lines.Item,
         Inventory.ItemAlternative,
         Inventory.OnHandPhys,
         Inventory.Allocated,
         Inventory.CostPrice,
         replace(vInventoryOptional2Values.Value, '/', '') AS Brand,
         Inventory.Optional3 AS StockStatus
    (SELECT SUM(lines.Invoiced)*-1 AS QtySoldInPeriod
     FROM (lines.item
           WHERE Lines.Date &gt;= '2014-01-01'
             AND Lines.Date &lt;= '2014-01-31' ) )
  FROM lines
  INNER JOIN inventory ON lines.item = Inventory.item
  INNER JOIN vInventoryOptional2Values ON Inventory.Optional2 = vInventoryOptional2Values.RecordID
  WHERE Inventory.Item = 'a158wa'
    AND lines.Invoiced &lt; 0
    AND Inventory.Status = 0
    AND Inventory.Optional3 &lt;&gt; 3
  GROUP BY Lines.Item,
           Inventory.Optional3,
           Inventory.ItemAlternative,
           Inventory.OnHandPhys,
           Inventory.CostPrice,
           vInventoryOptional2Values.Value,
           Inventory.Allocated
  </code></pre>