| Author |
 Topic  |
|
|
rypi
Yak Posting Veteran
51 Posts |
 Posted - 05/24/2012 : 13:39:01
|
I have the following query:
SELECT
ProjectId,
ISNULL([Sunday], 0) AS Sunday,
ISNULL([Monday], 0) AS Monday,
ISNULL([Tuesday], 0) AS Tuesday,
ISNULL([Wednesday], 0) AS Wednesday,
ISNULL([Thursday], 0) AS Thursday,
ISNULL([Friday], 0) AS Friday,
ISNULL([Saturday], 0) AS Saturday
FROM
(SELECT
TS.ProjectId,
dbo.GetWeekDayNameOfDate(TS.Date) AS [Day],
TS.Hours AS [HOURS]
FROM
Timesheet T,
TimesheetSegment TS
WHERE
T.EmployeeId = 4 AND
T.StartDate = '2012-05-20' AND
TS.TimesheetId = T.ID
GROUP BY
TS.ProjectId, dbo.GetWeekDayNameOfDate(TS.Date), TS.Hours
)
p PIVOT ( SUM(Hours) FOR [DAY] IN ( [Sunday], [Monday], [Tuesday],
[Wednesday], [Thursday],
[Friday], [Saturday]) ) AS PivotTable
This will return something like this:
ProjectId Sunday Monday Tuesday Wednesday.......
2 0.00 5.00 3.00 5.00 ........
How can I add a Total Column to the end and add up all the hours from Sunday to Saturday?
Like so:
ProjectId Sunday Monday Tuesday Wednesday .... Total
2 0.00 5.00 3.00 5.00 .... 13.00
Thanks!
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 05/24/2012 : 13:45:36
|
SELECT
ProjectId,
ISNULL([Sunday], 0) AS Sunday,
ISNULL([Monday], 0) AS Monday,
ISNULL([Tuesday], 0) AS Tuesday,
ISNULL([Wednesday], 0) AS Wednesday,
ISNULL([Thursday], 0) AS Thursday,
ISNULL([Friday], 0) AS Friday,
ISNULL([Saturday], 0) AS Saturday,
ISNULL([Sunday], 0) +
ISNULL([Monday], 0) +
ISNULL([Tuesday], 0) +
ISNULL([Wednesday], 0) +
ISNULL([Thursday], 0) +
ISNULL([Friday], 0) +
ISNULL([Saturday], 0) AS Total
FROM
(SELECT
TS.ProjectId,
dbo.GetWeekDayNameOfDate(TS.Date) AS [Day],
TS.Hours AS [HOURS]
FROM
Timesheet T,
TimesheetSegment TS
WHERE
T.EmployeeId = 4 AND
T.StartDate = '2012-05-20' AND
TS.TimesheetId = T.ID
GROUP BY
TS.ProjectId, dbo.GetWeekDayNameOfDate(TS.Date), TS.Hours
)
p PIVOT ( SUM(Hours)
FOR [DAY] IN ( [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday]) ) AS PivotTableBy the way, you should consider using DATENAME(dw,TS.Date) instead of your UDF call, it will perform better on large data sets. |
 |
|
|
rypi
Yak Posting Veteran
51 Posts |
Posted - 05/24/2012 : 13:54:21
|
Perfect!
I will change to DateName as well.
Thanks!!
|
|
|
|
rypi
Yak Posting Veteran
51 Posts |
Posted - 05/25/2012 : 12:18:01
|
Would it also be possible to have a Total Hours Row?
So the results would look something like this:
ProjectId Sunday Monday Tuesday Wednesday .... Total
2 0.00 5.00 3.00 5.00 .... 13.00
5 0.00 3.00 5.00 3.00 .... 11.00
Total Hours 0.00 8.00 8.00 8.00 .... 24.00
Thanks! |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 05/25/2012 : 13:56:16
|
| Take a look at ROLLUP and GROUPING SETS (SQL 2008+). It would take some finagling with PIVOT but should be possible. Also look at the GROUPING() function, you can use that in a CASE expression to identify the summary rows and apply the "Total Hours" value to it. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 05/26/2012 : 01:48:56
|
Take a look at using ANSI Standard Functions, like COALESCE instead of ISNULL
|
|
|
| |
Topic |
|
|
|
SUM of Columns
