Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using multiple PIVOTs JOINs and GROUP BYs for adva

Author  Topic 

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2011-10-11 : 05:02:59
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:



This is performed with the following SQL:


SELECT
*
FROM
(
SELECT
T.UserId
/* Group by these */
T.RoleId, T.GeographyId, T.DutyCategoryId,
TDA.DutyActivityId AS TypeId,
/* Total time spend on each Duty Activity */
SUM(TDA.PeriodMinutes) AS Total
FROM
Timesheet AS T

INNER JOIN
Officer AS O ON
O.OfficerId = T.OfficerId

/* Duty activity recorded for each timesheet */
LEFT JOIN
TimesheetDutyActivity AS TDA ON
TDA.TimesheetId = T.TimesheetId

WHERE
T.DutyStart >= '2011/05/01' AND
T.DutyEnd < '2011/10/10'

GROUP BY
T.OfficerId,
T.RoleId,T.GeographyId,T.DutyCategoryId,
TDA.DutyActivityId
) AS SourceTable

/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([7],,[9],[10],[11],[12],[13],[14],[15],[16])
)
AS PivotType

ORDER BY UserId ASC


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:



From this SQL:


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

INNER JOIN
Officer AS O ON
O.OfficerId = T.OfficerId

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

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

WHERE
T.DutyStart >= '2011/05/01' AND
T.DutyEnd < '2011/10/10'

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

/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([7],,[9],[10],[11],[12],[13],[14],[15],[16])
)
AS PivotType

/* Also PIVOT against the known AttributeIds */
PIVOT
(
SUM(AttributeId)
FOR AttributeId IN ([0],[-36],[-37],[-38],[-39],[-40],[-41],[-42],[-43],[-44],[-45],[-46],[-47],[-48],[-49],[-50],[-51],[-52],[-53],[-54],[-55],[-56],[-57],[-58],[-59],[-60],[-61],[-62],[-63],[-64],[-65],[-66],[-67],[-68],[-70],[-71],[-72],[-73],[-74],[-75],[-76],[-77],[-78],[-79],[-80],[-81],[-82],[-83],[-84],[-85],[-86],[-87],[-88],[-89],[-90],[-91],[-92],[-93],[-94],[-95],[-96],[-98],[-99],[-100],[-101],[-102],[-103],[-104],[-105],[-106],[-107],[-108],[-109],[-110],[-225],[-226])
)
AS PivotAttribute

ORDER BY UserId ASC


However the join I'm using isn't producing the correct results and the strange way I'm multiplying the `AttributeId` by -1 in order to create multiple PIVOTs doesn't seem right.

What is the correct way to achieve a report with grouping by UserId, RoleId, GeographyId, DutyCategoryId but also by the multiple AttributeIds recorded for each timesheet?

Database diagram of the relevant tables is shown below:



My upfront thanks to any gurus who can help me with this tricky one!

Kind regards
Pete

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 05:23:48
so how should be your output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2011-10-11 : 06:15:16
The first output screenshot shows the SUM of PeriodMinutes for each TimesheetDutyActivity that is being grouped by UserId, RoleId, GeographyId and CategoryId - this works.

What I'd like to do is also GROUP BY and return the AttributeIds for each group of timesheets returned.

So this would mean an additional set of columns in the output, one for each possible AttritubeId and then a 1 or 0 indicating if it exists for that grouping of timesheets.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 07:25:59
ok. so you've asking for multiple crosstab. in that case you need to use like

SELECT
T.UserId AS UserId,T.RoleId,T.GeographyId,T.DutyCategoryId,
SUM(CASE WHEN TDA.DutyActivityId = 7 THEN TDA.PeriodMinutes ELSE 0 END) AS [7],
SUM(CASE WHEN TDA.DutyActivityId = 8 THEN TDA.PeriodMinutes ELSE 0 END) AS ,
SUM(CASE WHEN TDA.DutyActivityId = 9 THEN TDA.PeriodMinutes ELSE 0 END) AS [9],..
SUM(CASE WHEN TDA.DutyActivityId = 16 THEN TDA.PeriodMinutes ELSE 0 END) AS [16],
MAX(CASE WHEN TA.AttributeId * -1 = 0 THEN 1 ELSE 0 END) AS [-1],
MAX(CASE WHEN TA.AttributeId * -1 = -36 THEN 1 ELSE 0 END) AS [-36],
MAX(CASE WHEN TA.AttributeId * -1 = -37 THEN 1 ELSE 0 END) AS [-37],
...
MAX(CASE WHEN TA.AttributeId * -1 = -226 THEN 1 ELSE 0 END) AS [-226]
FROM
Timesheet AS T

INNER JOIN
Officer AS O ON
O.OfficerId = T.OfficerId

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

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

WHERE
T.DutyStart >= '2011/05/01' AND
T.DutyEnd < '2011/10/10'

GROUP BY
T.UserId,T.RoleId,T.GeographyId,T.DutyCategoryId



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2011-10-11 : 09:49:48
Thanks for your interest visakh16, however in my haste to provide information on my database, I've done a poor job of explaining the actual problem I'm having.

I've re-written my question from scratch below and created a ZIP containing an example database:


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: [URL]http://dl.dropbox.com/u/26428893/Timesheet%20database%20example.zip[/URL]

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



Report on timesheets, without attributes grouping
[CODE]
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
[/CODE]

Report on timesheets, with attempted attributes grouping
[CODE]
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
[/CODE]


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 13:10:55
[code]
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

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

ORDER BY UserId ASC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2011-10-12 : 08:53:48
Visakh16, you have my sincere thanks for your help.

I've had to tweak your SQL, but you've provided the key - which is your LEFT JOIN statement for each AttributeId.

Again, thank you
Pete
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 09:01:32
no problem
happy that you sorted it out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -