Complex Subtotals needed for in house report
I need a query that returns subtotals by MemberName(PersonID) but broke out into the 2 different ContactTypes(11 & 12 under IsFaceToFace). The below query gets me the base data I need without any subtotals.
I tried messing around with WITH ROLLUP and PARTITION BY 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.
Also, my ultimate goal is to provide this in Excel 2007 for our in house Finance team so I am very open to ANY solution. Be it SQL, LINQ(though this uses 2 db's), Reporting Services, Excel Macro, C#, etc. I just need it to work at this point.
What are my best options? This is a sample of what is currently returned.
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
Normally, I'd suggest a straight aggregate function, but you've got some complex joins going on and I don't know which fields belong to which table.
This is how I'd do it.
Create a temp table :-
DECLARE @contacts TABLE ( ClientFolder int ,MemberName varchar(256) ,ContactDate datetime ,TimeSpent decimal(10,1) ,IsFaceToFace int ) INSERT INTO @contacts ( ClientFolder ,MemberName ,ContactDate ,TimeSpent ,IsFaceToFace ) -- Your select statement
SELECT MemberName ,IsFaceToFace ,SUM(TimeSpent) AS TimeSpent ,MAX(ContactDate) AS ContactDate FROM @contacts GROUP BY MemberName, IsFaceToFace
It's fast at the cost of a temp table overhead, and you can use the table again if you need to.
As for reporting, you could employ Microsoft SQL Server Reporting Services, if available. Some versions have their problems, but it does have an Excel export facility that should be fine for output of this simplicity.
It also benefits from a subscription facility, whereby you can arrange timed runs of the report to be delivered to users, either by mail or placed in a file share.
You really have 2 options if you want keep it in SQL, which I think will be the quickest way to do it. You can either use PARTITION BY or you can insert your Query results into a temp table and then query them a second time. All using Partition By really does is reduce the number of SQL statements you need to have from 2 to 1 and remove the temp table. To use the temp table just insert the result of your above query into it and then do a SELECT from it and group by PersonID and IsFaceToFace.