SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I don't think Pivot is going to work


I don't think Pivot is going to work

Author
Message
rick.staples
rick.staples
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
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
J Livingston SQL
J Livingston SQL
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27006 Visits: 41300
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
drew.allen
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36505 Visits: 13586
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
sgmunson
sgmunson
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43156 Visits: 5422
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)
Smile Smile Smile
Health & Nutrition
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)

Group: General Forum Members
Points: 504509 Visits: 44233
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search