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.
| 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/
|
 |
|
|
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. |
|
|
|
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/
|
|
|
|
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]
|
|
|
|
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/
|
|
|
|
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 |
|
|
|
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/
|
|
|
|
|
|
|
|
|
Using multiple PIVOTs JOINs and GROUP BYs for adva