query help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query help!

Author

Topic

sneeze24
Starting Member

4 Posts

Posted - 2004-07-02 : 10:34:48

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

Now i need to find a way (without having to add a new table) to Select the total hours a managers group worked for a certain month period.can anyone help please.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-02 : 10:42:36

WE will need to know how you define manager's groups, are they supervisor_id ?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

sneeze24
Starting Member

4 Posts

Posted - 2004-07-02 : 10:42:36

the last sentence was a little unclear sorry, i need to find a way to view all of the project sums summed together for a group of users whose supervisors id is say "id1" where id1 is the Director

sneeze24
Starting Member

4 Posts

Posted - 2004-07-02 : 10:44:11

each of the users with supervisor id=id1 is a manager so i need each of them to display the monthly sum for their group. am i making sense yet?

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-02 : 10:49:23

so, you want:
time per month, per project, per supervisor_id
or
time per supervisor_id ?


SELECT Entry.Supervisor_ID, 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].Supervisor_Id = @SV_CUID) 
GROUP BY [user].Supervisor_ID, Project.ProjectName


SELECT Entry.Supervisor_ID, 
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].Supervisor_Id = @SV_CUID) 
GROUP BY [user].Supervisor_ID

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

sneeze24
Starting Member

4 Posts

Posted - 2004-07-02 : 10:56:37

ok Supervisor "id1" wants to look at the monthly totals for the manager groups under him. so time per month(of all projects) per supervisor_id.

so if Manager1 looked at his totals its like this

Project Name Jan Feb Mar .....
Project1 10 25 30
Project2 35 17 40

if Manager1's Director looks at his report it looks like this

Manager Group Jan Feb Mar.....
Manager1 45 42 70
Manager2 50 60 30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources