title: T-SQL - aggregate in all rows using aggregate from correlated sub-query
body: <pre><code>store   item   datekey   onhand   salesunits
  --------------------------------------------
  001     A      50        65       2
  001     A      51        8        4
  001     A      52        0        8
  --------------------------------------------
  </code></pre>
  
  <p>What I need to accomplish: to get the latest onhand greater than zero minus the total units sold, by store and item. So in the example above it would be 8-14=-6.</p>
  
  <p>I am using a correlated sub-query to determine the latest datekey and then joining back to the main query. But obviously by doing so I lose the data related to the other rows necessary to sum the salesunits:</p>
  
  <p>This is what I have and it's wrong:</p>
  
  <pre><code>select s1.Store, s1.Item, s1.OnHand, sum(salesunit)
  from sales s1
    join (select top 1 store,item, max(DateKey) as datekey
          from sales
          where isnull(onhand,0) &gt; 0
            and DateKey in (50,51,52)
          group by store, item) s2  on s2.store=s1.store and s2.item=s1.item and s2.datekey=s1.datekey
  group by s1.Store, s1.Item, s1.OnHand
  </code></pre>
  
  <p>Thanks for your help!</p>