Select Statement that groups a group? I don't know how to ask.
Select Statement that groups a group? I don't know how to ask.
(OP)
Select PID, ProjectName, UserName, Sum(Hours)
From WorkLog
WorkLog:
PID | projectName | UserName | HourS |
----------------------------------------
1 | Fist Project | Joe User | 2 |
1 | Fist Project | Joe User | 1 |
1 | Fist Project | BOB Cool | 2 |
2 | Second Proj | Joe User | 1 |
2 | Second Proj | Joe User | 3 |
1 | Fist Project | BOB Cool | 2 |
I am trying to produce this result:
PID | projectName | UserName | SUM of HourS |
-------------------------------------------------
1 | Fist Project | Joe User | 3 |
1 | Fist Project | BOB Cool | 5 |
2 | Second Proj | Joe User | 4 |
Thank you for your time. I am sure this is easy, but I am new. I promise I have looked for this answer before posting this new question.
From WorkLog
WorkLog:
PID | projectName | UserName | HourS |
----------------------------------------
1 | Fist Project | Joe User | 2 |
1 | Fist Project | Joe User | 1 |
1 | Fist Project | BOB Cool | 2 |
2 | Second Proj | Joe User | 1 |
2 | Second Proj | Joe User | 3 |
1 | Fist Project | BOB Cool | 2 |
I am trying to produce this result:
PID | projectName | UserName | SUM of HourS |
-------------------------------------------------
1 | Fist Project | Joe User | 3 |
1 | Fist Project | BOB Cool | 5 |
2 | Second Proj | Joe User | 4 |
Thank you for your time. I am sure this is easy, but I am new. I promise I have looked for this answer before posting this new question.
RE: Select Statement that groups a group? I don't know how to ask.
CODE
, projectname
, username
, SUM(hours) AS sum_of_hours
FROM WorkLog
GROUP
BY pid
, projectname
, username
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Select Statement that groups a group? I don't know how to ask.
You are everywhere. Thank you again.
RE: Select Statement that groups a group? I don't know how to ask.
Can you help me take this farther? This query below partially works. The only two problems I have is that I can't return the ProjectName, which I know is easy.
But most importantly, the MgtHrs is showing the Resource hours for the project no matter who the Manager is. I need to show resource hours as '0' if the [User].UserName != Projects.Manager
SELECT ProjectID, MAX(MgtHrs) as MgtHrs, MAX(RHrs) as RHrs, SUM(WHrs) as WHrs, (MAX(MgtHrs) + MAX(RHrs)) AS TOTALHOUR, UserName
FROM
(SELECT Projects.ProjectName, Projects.ProjectID, isnull(Projects.ResourceHours, 0) as MgtHrs, isnull(Resources.FTE, 0) as RHrs, isnull(WorkLog.WorkTime, 0) as WHrs, [User].UserName
FROM Projects FULL OUTER JOIN
Resources ON Projects.ProjectID = Resources.ProjectID FULL OUTER JOIN
WorkLog ON Projects.ProjectID = WorkLog.ProjectID LEFT JOIN
[User] ON Projects.Manager LIKE [User].UserName OR WorkLog.UserName LIKE [User].UserName OR Resources.Resource LIKE [User].UserName
WHERE Projects.ProjectID = '38'
) AS MyTable
Group by ProjectID,UserName
Order by UserName
Producing:
ProjectID MgtHrs, RHrs, WHrs, TOTALHOUR, UserName
38 150 40 35.5000 190 Joe
38 150 150 71.0000 300 Bob
I want it to produce:
ProjectID MgtHrs, RHrs, WHrs, TOTALHOUR, UserName
38 0 40 35.5000 40 Joe
38 150 150 71.0000 300 Bob
RE: Select Statement that groups a group? I don't know how to ask.
CODE
SELECT ProjectName, ProjectID, MAX(MgtHrs) as MgtHrs, MAX(RHrs) as RHrs, SUM(WHrs) as WHrs, (MAX(MgtHrs) + MAX(RHrs)) AS TOTALHOUR, UserName
FROM
(SELECT Projects.ProjectName, Projects.ProjectID, case when Projects.Manager Like [User].UserName then isnull(Projects.ResourceHours, 0) else 0 end as MgtHrs, isnull(Resources.FTE, 0) as RHrs, isnull(WorkLog.WorkTime, 0) as WHrs, [User].UserName
FROM Projects FULL OUTER JOIN
Resources ON Projects.ProjectID = Resources.ProjectID FULL OUTER JOIN
WorkLog ON Projects.ProjectID = WorkLog.ProjectID LEFT JOIN
[User] ON Projects.Manager LIKE [User].UserName OR WorkLog.UserName LIKE [User].UserName OR Resources.Resource LIKE [User].UserName
WHERE Projects.ProjectID = '38'
) AS MyTable
Group by ProjectID,UserName,ProjectName
Order by UserName
-----------------------------------------
I cannot be bought. Find leasing information at http://www.joshaxtell.com/
RE: Select Statement that groups a group? I don't know how to ask.
SELECT MyTable2.ProjectID, Projects.ProjectName, MyTable2.MgtHrs, MyTable2.RHrs, MyTable2.WHrs, MyTable2.TOTALHOUR, MyTable2.UserNameFrom(SELECT ProjectID, MAX(MgtHrs) as MgtHrs, MAX(RHrs) as RHrs, SUM(WHrs) as WHrs, (MAX(MgtHrs) + MAX(RHrs)) AS TOTALHOUR, UserNameFROM(SELECT Projects.ProjectName, Projects.ProjectID,isnull(case when [User].UserName IS NOT NULL and Projects.Manager LIKE [User].UserName then Projects.ResourceHours else 0 end, 0) as MgtHrs,isnull(Resources.FTE, 0) as RHrs, isnull(WorkLog.WorkTime, 0) as WHrs, [User].UserNameFROM Projects FULL OUTER JOIN Resources ON Projects.ProjectID = Resources.ProjectID FULL OUTER JOIN WorkLog ON Projects.ProjectID = WorkLog.ProjectID LEFT JOIN [User] ON Projects.Manager LIKE [User].UserName OR WorkLog.UserName LIKE [User].UserName OR Resources.Resource LIKE [User].UserNameWhere ([User].UserName LIKE '%') AND (Projects.ProjectID = '38')) AS MyTableGroup by ProjectID,UserName) AS MyTable2Inner Join Projects On Projects.ProjectID = MyTable2.ProjectIDOrder by MyTable2.UserName
SELECT MyTable2.ProjectID, Projects.ProjectName, MyTable2.MgtHrs, MyTable2.RHrs, MyTable2.WHrs, MyTable2.TOTALHOUR, MyTable2.UserName
From
(SELECT ProjectID, MAX(MgtHrs) as MgtHrs, MAX(RHrs) as RHrs, SUM(WHrs) as WHrs, (MAX(MgtHrs) + MAX(RHrs)) AS TOTALHOUR, UserName
FROM
(SELECT Projects.ProjectName, Projects.ProjectID,
isnull(case when [User].UserName IS NOT NULL and Projects.Manager LIKE [User].UserName then Projects.ResourceHours else 0 end, 0) as MgtHrs,
isnull(Resources.FTE, 0) as RHrs, isnull(WorkLog.WorkTime, 0) as WHrs, [User].UserName
FROM Projects FULL OUTER JOIN
Resources ON Projects.ProjectID = Resources.ProjectID FULL OUTER JOIN
WorkLog ON Projects.ProjectID = WorkLog.ProjectID LEFT JOIN
[User] ON Projects.Manager LIKE [User].UserName OR WorkLog.UserName LIKE [User].UserName OR Resources.Resource LIKE [User].UserName
Where ([User].UserName LIKE '%') AND (Projects.ProjectID = '38')
) AS MyTable
Group by ProjectID,UserName
) AS MyTable2
Inner Join Projects On Projects.ProjectID = MyTable2.ProjectID
Order by MyTable2.UserName
RE: Select Statement that groups a group? I don't know how to ask.
SELECT pid
, projectname
, username
, SUM(hours) AS sum_of_hours
FROM WorkLog
GROUP BY pid
, projectname
, username
become the monstrosity in your last post?
If this is for a commercial application, the manager of the support department that has to take this on might well ask you to re-write it!