I don't think Pivot is going to work

Question

I don't think Pivot is going to work

rick.staples

Newbie

Points: 4
More actions
December 22, 2017 at 11:04 am

#342172

I have a list of users who but time in on a daily basis, I want to Sum it er week and have my 52 weeks go across horizontal (one row for user then week1 - week52 in columns with Sum of hours for each week,
EX:
User WK1 WK2 WK3 . . .
Sally 41 45 38 . . .
Joe 52 32 41 . . .
HBelow is what I have to get the data But it is all in two columns
employee week hours
Sa;;y 1 41
Sally 2 45
sally 3 38
Joe 1 52
Joe 2 32
Joe 3 41

select EmployeeID, SUM(TEHours) as Hours, Datepart(wk,TEdate) AS Week from TimeEntry
where TEdate like '%2017%'
Group by EmployeeID , Datepart(wk,TEdate)
Order by EmployeeID, week
I tried to run Pivot but I keep getting a syntax error. I read in another thread that you could not use pivot for three fields.
With the above code I can use a pivot table in excel and get what I need but I really want to run it all in Sql

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

hi....please take a look here https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

drew.allen SSC Guru Points: 76944 More actions · Answer 2

rick.staples - Friday, December 22, 2017 11:04 AM
I have a list of users who but time in on a daily basis, I want to Sum it er week and have my 52 weeks go across horizontal (one row for user then week1 - week52 in columns with Sum of hours for each week,
EX:
User WK1 WK2 WK3 . . .
Sally 41 45 38 . . .
Joe 52 32 41 . . .
HBelow is what I have to get the data But it is all in two columns
employee week hours
Sa;;y 1 41
Sally 2 45
sally 3 38
Joe 1 52
Joe 2 32
Joe 3 41

select EmployeeID, SUM(TEHours) as Hours, Datepart(wk,TEdate) AS Week from TimeEntry
where TEdate like '%2017%'
Group by EmployeeID , Datepart(wk,TEdate)
Order by EmployeeID, week
I tried to run Pivot but I keep getting a syntax error. I read in another thread that you could not use pivot for three fields.
With the above code I can use a pivot table in excel and get what I need but I really want to run it all in Sql

A pivot will work, because you are only aggregating one field (not three). It will be something like

SELECT EmployeeID, [1] AS Wk1, [2] AS Wk2, .... FROM ( SELECT EmployeeID, DATEPART(WK, TEDate) AS WK, TEHours FROM TimeEntry) PIVOT( SUM(TEHours) FROM WK in ([1], [2], ....)

That being said, you may want to consider some other option, such as SSRS to produce this report.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sgmunson SSC Guru Points: 110639 More actions · Answer 3

drew.allen - Friday, December 22, 2017 12:54 PM
rick.staples - Friday, December 22, 2017 11:04 AM
I have a list of users who but time in on a daily basis, I want to Sum it er week and have my 52 weeks go across horizontal (one row for user then week1 - week52 in columns with Sum of hours for each week,
EX:
User WK1 WK2 WK3 . . .
Sally 41 45 38 . . .
Joe 52 32 41 . . .
HBelow is what I have to get the data But it is all in two columns
employee week hours
Sa;;y 1 41
Sally 2 45
sally 3 38
Joe 1 52
Joe 2 32
Joe 3 41

select EmployeeID, SUM(TEHours) as Hours, Datepart(wk,TEdate) AS Week from TimeEntry
where TEdate like '%2017%'
Group by EmployeeID , Datepart(wk,TEdate)
Order by EmployeeID, week
I tried to run Pivot but I keep getting a syntax error. I read in another thread that you could not use pivot for three fields.
With the above code I can use a pivot table in excel and get what I need but I really want to run it all in Sql
A pivot will work, because you are only aggregating one field (not three). It will be something like
SELECT EmployeeID, [1] AS Wk1, [2] AS Wk2, .... FROM ( SELECT EmployeeID, DATEPART(WK, TEDate) AS WK, TEHours FROM TimeEntry) AS a PIVOT( SUM(TEHours) FOR WK in ([1], [2], ....) ) AS pvt
That being said, you may want to consider some other option, such as SSRS to produce this report.
Drew

Note the spelling change and additional ) with table alias for the pivot.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

rick.staples - Friday, December 22, 2017 11:04 AM
I have a list of users who but time in on a daily basis, I want to Sum it er week and have my 52 weeks go across horizontal (one row for user then week1 - week52 in columns with Sum of hours for each week,
EX:
User WK1 WK2 WK3 . . .
Sally 41 45 38 . . .
Joe 52 32 41 . . .
HBelow is what I have to get the data But it is all in two columns
employee week hours
Sa;;y 1 41
Sally 2 45
sally 3 38
Joe 1 52
Joe 2 32
Joe 3 41

select EmployeeID, SUM(TEHours) as Hours, Datepart(wk,TEdate) AS Week from TimeEntry
where TEdate like '%2017%'
Group by EmployeeID , Datepart(wk,TEdate)
Order by EmployeeID, week
I tried to run Pivot but I keep getting a syntax error. I read in another thread that you could not use pivot for three fields.
With the above code I can use a pivot table in excel and get what I need but I really want to run it all in Sql

I can take a look at this tonight. It's actually quite easy but to ask a question... do all of these people work for the same department and would it be handy to have subtotals by department along with a grand total row? Ok... so two questions... would it also be handy to have a total column to show the total hours each person worked for the year?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)