Using multiple PIVOTs JOINs and GROUP BYs for advanced report generation

Question

I've developed a timesheet recording system, that allows users to record:

• A Period of time (9:00 to 12:00)
• What they did during that time (DutyActivity: 1 hour report writing, 2 hours training)

When they submit a timesheet, the system also records their GeographyId, RoleId, CategoryId (single ids) and AttributeIds (multiple ids) - which is a snapshot of the user at that point in time.

This recorded timesheet information can then be queried for reports. In it's simplest form the system will return a list of UserIds with a SUM of the PeriodMinutes for each recorded DutyActivityId. Which would look like the following:

However what I also want to do is group by and return the recorded AttributeIds recorded with each timesheet (in the TimesheetAttribute table). However because there are multiple AttributeIds recorded for each timesheet, it's a little more complex.

My attempt at this creates the following report:

However the problem with this is that the SUM(PeriodMinutes) for each DutyCategoryId returned is now too high. It appears my SQL to include AttributeIds for each timesheet is causing the SUM calculation to be made incorrectly.

I've created a ZIP containing SQL for the database schema plus data, along with the SQL for both queries I've screenshot'd here: ZIP Timesheet example database

The information below is included in the ZIP, but I'm linking them here for ease of access:

Report on timesheets, without attributes grouping

SELECT
*
FROM
(
SELECT
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId AS TypeId,
SUM(TDA.PeriodMinutes) AS Total
FROM 
Timesheet AS T

LEFT JOIN 
TimesheetDutyActivity AS TDA ON
TDA.TimesheetId = T.TimesheetId

GROUP BY
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId
) AS SourceTable

/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([1],[2],[3],[4],[5])
)
AS PivotType

ORDER BY UserId ASC

Report on timesheets, with attempted attributes grouping

SELECT
*
FROM
(
SELECT
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TA.AttributeId * -1 AS AttributeId, /* Multiply AttributeId by -1 in order to create negative Ids, so that two PIVOT operations can be used */
TDA.DutyActivityId AS TypeId,
SUM(TDA.PeriodMinutes) AS Total

FROM 
Timesheet AS T

LEFT JOIN 
TimesheetAttribute AS TA ON
TA.TimesheetId = T.TimesheetId

LEFT JOIN 
TimesheetDutyActivity AS TDA ON
TDA.TimesheetId = T.TimesheetId

GROUP BY
T.UserId,
AttributeId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId
) AS SourceTable

/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([1],[2],[3],[4],[5])
)
AS PivotType

/* Also PIVOT against the known AttributeIds */
PIVOT
(
SUM(AttributeId)
FOR AttributeId IN ([-1],[-2],[-3],[-4],[-5])
)
AS PivotAttribute

ORDER BY UserId ASC

Answer 1

I've managed to get the required result by dropping the second PIVOT and AttributeId GROUP BY argument and instead using a LEFT JOIN on the attributes.

This answer was provided by visahk16 on the SQL Team forums:

SELECT
    *
    FROM
    (
    SELECT
    T.UserId,
    T.RoleId,T.GeographyId,T.CategoryId,
    TA.[-1],TA.[-2],TA.[-3],TA.[-4],TA.[-5],
    TDA.DutyActivityId AS TypeId,
    SUM(TDA.PeriodMinutes) AS Total

    FROM 
    Timesheet AS T

    LEFT JOIN 
    (SELECT TimesheetId,
     SUM(CASE WHEN AttributeId ='1' THEN -1 ELSE 0 END) AS [-1],
     SUM(CASE WHEN AttributeId ='2' THEN -1 ELSE 0 END) AS [-2],
...
     SUM(CASE WHEN AttributeId ='5' THEN -1 ELSE 0 END) AS [-5] 
     FROM TimesheetAttribute 
     GROUP BY TimesheetId 
     )AS TA ON
    TA.TimesheetId = T.TimesheetId

    LEFT JOIN 
    TimesheetDutyActivity AS TDA ON
    TDA.TimesheetId = T.TimesheetId

    GROUP BY
    T.UserId,
    AttributeId,
    T.RoleId,T.GeographyId,T.CategoryId,
    TDA.DutyActivityId
    ) AS SourceTable

    /* PIVOT against the known DutyActivityIds */
    PIVOT
    (
    SUM(Total)
    FOR TypeId IN ([1],[2],[3],[4],[5])
    )
    AS PivotType

    ORDER BY UserId ASC