title: Complex Subtotals needed for in house report
body: <p>I need a query that returns subtotals by <strong>MemberName</strong>(<em>PersonID</em>) but broke out into the 2 different <strong>ContactTypes</strong>(<em>11 &amp; 12 under IsFaceToFace</em>).  The below query gets me the base data I need without any subtotals.  </p>
  
  <p>I tried messing around with <code>WITH ROLLUP</code> and <code>PARTITION BY</code> but those are new to me and never worked completely right.  I am sure I will need to provide more info on table structure but maybe not.</p>
  
  <p>Also,  my ultimate goal is to provide this in Excel 2007 for our in house Finance team so I am very open to <strong>ANY</strong> solution.  Be it SQL, LINQ(<em>though this uses 2 db's</em>), Reporting Services, Excel Macro, C#, etc.  I just need it to work at this point. </p>
  
  <p>What are my best options?
  This is a sample of what is currently returned.</p>
  
  <pre><code>ClientFolder MemberName        ContactDate  TimeSpent IsFaceToFace
  
  68933   Collins,Vickie          2010-01-07  0.2    11
  68937   Pervin,Jennifer         2010-01-07  0.2    11
  68937   Pervin,Jennifer         2010-01-11  0.1    11
  68937   Pervin,Jennifer         2010-01-12  0.1    11
  69861   Klum,Ronald             2010-01-04  0.3    11
  69861   Klum,Ronald             2010-01-04  0.3    11
  69861   Klum,Ronald             2010-01-07  0.2    11
  69861   Klum,Ronald             2010-01-11  0.2    11
  70205   Matamoros,Joellen       2010-01-04  0.5    11
  70205   Matamoros,Joellen       2010-01-06  0.8    11
  70205   Matamoros,Joellen       2010-01-06  2.4    12
  70205   Matamoros,Joellen       2010-01-07  0.7    11
  70205   Matamoros,Joellen       2010-01-11  0.2    11
  70205   Matamoros,Joellen       2010-01-11  1.0    11
  70205   Matamoros,Joellen       2010-01-12  0.3    11
  
  
  
  USE MATRIX
  
  SELECT  ClientFolder = (select distinct tblApplicationAssociation.PersonApplicationID from Connect.dbo.tblApplicationAssociation where Connect.dbo.tblApplicationAssociation.ApplicationID = 6 AND Connect.dbo.tblApplicationAssociation.PersonID = MATRIX.dbo.tblCaseNotes.PersonID)
          ,MemberName = (select tblPerson.LastName + ',' + tblPerson.FirstName from Connect.dbo.tblPerson where Connect.dbo.tblPerson.PersonID = MATRIX.dbo.tblCaseNotes.PersonID)
          ,ContactDate
          ,TimeSpent = SUM(TimeSpentUnits)
          ,CASE WHEN ContactTypeID = 3 THEN '12' ELSE '11' END AS IsFaceToFace
  
  FROM tblCaseNotes
          LEFT OUTER JOIN tblCaseNoteContactType
                  ON tblCaseNotes.CaseNoteID = tblCaseNoteContactType.CaseNoteID
  
  WHERE InsertUser = 'pschaller' -- this will be a variable for Current User
  AND 
  ContactDate BETWEEN '01/01/2010' AND '01/31/2010' -- this will be two Date variables
  
  GROUP BY ContactDate, ContactTypeID, PersonID
  ORDER BY PersonID, ContactDate, ContactTypeID
  </code></pre>