title: Tricky SQL grouping query
body: <p>I have a table which tracks items dispensed.  The main data I have is a TransactionID, TransactionDateTime, PersonID, CostCentre, ProductID, TransactionType, and Quantity.</p>
  
  <p>I need to transform this table to only show the items dispensed and used.</p>
  
  <p>Now my issue is that in some cases, a product may be distributed, say 25 of that item, and then for some reason it may be returned, either entirely, or partially.  So either 25 come back, or 15 come back.  The resulting record needs to identify the correct amount distributed.  </p>
  
  <p>To signify a return, the Quantity field is negative, and the TransactionType is DSPN-  as oppose to DSPN for a standard transaction.</p>
  
  <p>In most cases the TransactionID is the same for both the dispensed and return transaction, resulting in a simple Group By function.  However I have found cases where this isn't the case, and was wondering if there is another solution to grouping these records together.</p>
  
  <p>What I have so far is just the basic group.</p>
  
  <p><code>SELECT dspn_id AS DspnID, SUM([Qty]) AS Qty,  MIN(creationDate) AS RIO, MIN(TransactionID) AS PKey
      FROM MyTable
      GROUP BY dspn_id
      HAVING SUM(Qty) &gt; 0
  </code></p>
  
  <p>Sample data of standard situation where I can group by dspn_id:<code>
  dspn_id      creationDate       PersonID  CostCenter ProdID  TranType   QTY
  972490  2012-10-08 12:14:46.440 984254875   CostCC1 4591    DSPN    25
  972487  2012-10-08 12:14:46.463 984254875   CostCC1 1017    DSPN    30
  972490  2012-10-08 12:15:24.270 984254875   CostCC1 4591    DSPN-   -25
  972487  2012-10-08 12:15:38.990 984254875   CostCC1 1017    DSPN-   -30
  972496  2012-10-08 12:15:51.490 984254875   CostCC1 4591    DSPN    25
  972497  2012-10-08 12:15:51.500 984254875   CostCC1 1017    DSPN    30</code></p>
  
  <p>Sample data of where my issue is:<code>
  dspn_id      creationDate       PersonID  CostCenter ProdID  TranType   QTY
  962376  2012-09-21 16:01:03.783 123456789   CostCC2 18007   DSPN    2
  962998  2012-09-24 09:31:20.433 123456789   CostCC2 18007   DSPN-   -1</code></p>
  
  <p>I could simply group up by CostCenter and ProdID, but this is not the solution I'm after.  Ideally, in the above situation, I would have a single record for ProdID 18007 that shows a date of 2012-09-21 16:01:03.783 and QTY of 1.  Even if that same ProdID was distributed to that PersonID with that CostCentre.</p>