```
store   item   datekey   onhand   salesunits
--------------------------------------------
001     A      50        65       2
001     A      51        8        4
001     A      52        0        8
--------------------------------------------

```

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.

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:

This is what I have and it's wrong:

```
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) > 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

```

Thanks for your help!