|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
July 2nd, 2004, 11:11 AM
|
|||
|
|||
|
query help
ok i have 3 tables in my DB: User,Entry,Project. The User has UserName, UserID, Supervisor_ID. The Entry has ProjectID,EntryID, UserID, Total Time. The Project table has ProjectID, ProjectName(Those are the relevant columns).
A Managers group works on specific projects and the manager can see the totals of those projects broken down bymonth with this query: SELECT Project.ProjectID, Project.ProjectName AS ProjectName, SUM(CASE WHEN DatePart(month , WorkDate) = 1 THEN TotTime ELSE 0 END) AS Jan, SUM(CASE WHEN DatePart(month , WorkDate) = 2 THEN TotTime ELSE 0 END) AS Feb, SUM(CASE WHEN DatePart(month , WorkDate) = 3 THEN TotTime ELSE 0 END) AS Mar, SUM(CASE WHEN DatePart(month , WorkDate) = 4 THEN TotTime ELSE 0 END) AS Apr, SUM(CASE WHEN DatePart(month , WorkDate) = 5 THEN TotTime ELSE 0 END) AS May, SUM(CASE WHEN DatePart(month , WorkDate) = 6 THEN TotTime ELSE 0 END) AS Jun, SUM(CASE WHEN DatePart(month , WorkDate) = 7 THEN TotTime ELSE 0 END) AS Jul, SUM(CASE WHEN DatePart(month , WorkDate) = 8 THEN TotTime ELSE 0 END) AS Aug, SUM(CASE WHEN DatePart(month , WorkDate) = 9 THEN TotTime ELSE 0 END) AS Sep, SUM(CASE WHEN DatePart(month , WorkDate) = 10 THEN TotTime ELSE 0 END) AS Oct, SUM(CASE WHEN DatePart(month , WorkDate) = 11 THEN TotTime ELSE 0 END) AS Nov, SUM(CASE WHEN DatePart(month , WorkDate) = 12 THEN TotTime ELSE 0 END) AS Dec FROM Entry INNER JOIN [User] ON Entry.CUID = [User].CUID INNER JOIN Project ON Entry.ProjectID = Project.ProjectID WHERE ([User].SV_CUID = @SV_CUID) GROUP BY Project.ProjectID, Project.ProjectName if manager1 is looking at his output it looks similar to this: ProjectName Jan Feb Mar.... Project1 10 15 5 Project2 5 20 10 now what i need is if a Director wants to view his manager groups totals similar to this: ManagerGroup Jan Feb Mar.... Manager 1 15 25 15 Manager2 20 30 10 as you can see Manager1's monthly totals are his project totals summed together. i need a query to output the second set of data without having to add a table to my database
|
|
| Viewing: Dev Articles Community Forums > Databases > Microsoft SQL Server > query help |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
