Complex Subtotals needed for in house report

Question

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

Answer 1

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.

Answer 2

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

Then,

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.