Databases
StackOverflow2013
Postsdbo
POComplex Subtotals needed for in house report
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POComplex Subtotals needed for in house report
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USRefracted Paladin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POComplex Subtotals needed for in house report
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIs there anything wrong with calculating the subtotals yourself after getting the data?
      singulars
      1. POComplex Subtotals needed for in house report
      1. USAnon.
    2. COYou mean manually in Excel? If that is what you meant then yeah. I need to let our internal users generate this report themselves. Thanks though
      singulars
      1. POComplex Subtotals needed for in house report
      1. USRefracted Paladin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload