Select Statement that groups a group? I don't know how to ask.

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

wow r937,
You are everywhere. Thank you again.

Ok,

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

I'm not sure this is ANSI compliant but I think CASE would work


-----------------------------------------
I cannot be bought. Find leasing information at

This is what I finally ended up using thanks to Naom in the asp.net forums.



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

No disrespect stinkyjak, and it may be the fact that you've not used the TGML CODE statement, but your code is almost unreadable. How did Rudy's:
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!