Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

Simple results

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:

Complex results

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:

Database diagram

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
share|improve this question
    
What would that report look like if you did it by hand? –  Mike Sherrill 'Cat Recall' Oct 10 '11 at 22:18
    
@Catcall It would look like the first output screenshot I've listed, which shows the total minutes of each DutyCategoryId grouped by UserId, RoleId, GeographyId and CategoryId - but with additional columns listing whether or not that group of timesheets had a value for each AttrituteId recorded against it (A boolean flag for each possible AttributeId –  Peter Bridger Oct 11 '11 at 9:06
2  
@PeterBridger, this is a fairly involved question. You're more likely to get responses if you add DDL and test data scripts so people have something to work with. –  Mark Storey-Smith Oct 11 '11 at 11:16
    
@Mark-Storey-Smith Yes good call - I've completely re-written the question to focus purely on the issue and attached a ZIP containing all the SQL to re-create the issues shown above. –  Peter Bridger Oct 11 '11 at 13:51

1 Answer 1

up vote 0 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.