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