CODEWITHSUNDEEP
sql
Aligator3000
Aligator3000

Reputation: 345

MAX Number of team members

Ok, so I'm dealing with this messy SQL statement.

What I am trying to do is, count the number of team members on a project (including project manager). When project details are created, members from the Members table are written to the ProjectIterationMembers table. A project can consist of 1 or many iterations.

What is happening right now is that when I add a second iteration and assign the same people to the project, I get two sets of data - Project name, 5....Project name, 10. How can I make sure that only the max count of members is displayed? Thanks in advance!

SELECT 
    DISTINCT(Project.ProjectName), 
    Project.ProjectID, 
    Project.Status, 
    Project.CompanyID, 
    Project.StartDate,   
    m.MemberID,
    m.FirstName + ' ' + m.LastName AS ProjectManager, 
   (SELECT COUNT(MemberID) 
        FROM ProjectIterationMember 
        WHERE ProjectIterationID = i.ProjectIterationID) AS MemberCount 
FROM 
   Project, 
   Member m, 
   ProjectIterationMember, 
   Iterations i 
WHERE 
    m.CompanyRole IN ('Project Manager', 'Admin') 
AND Project.CompanyID = '" + co_id + "'
AND m.MemberID = ProjectIterationMember.MemberID 
AND i.ProjectIterationID = ProjectIterationMember.ProjectIterationID 
AND i.ProjectID = Project.ProjectID ORDER BY Project.StartDate DESC

Upvotes: 1

Views: 151

Answers (2)

Thomas
Thomas

Reputation: 64645

Some notes:

  1. Do not use the syntax of separating tables with commas. Instead, use the ISO Join syntax.
  2. I haven't the foggiest idea what Distinct( Project.ProjectName ) would do. As far as I know, it would be invalid syntax in any database system.
  3. If you have multiple iterations for the same Project, which manager should show? I assumed the iteration with the largest member count.
  4. Which database product and version? Given that you did not say, I'm going to assume SQL Server 2005+
With CountByIteration As
    (
    Select PIM1.ProjectId, I1.ProjectIterationId
        , Count(*) As MemberCount
    From Iterations As I1
        Join ProjectIterationMembers As PIM1
            On PIM1.ProjectIterationId = I1.ProjectIterationId
    Group By PIM1.ProjectId, I1.ProjectIterationId
    )
    , RankedIterations As
    (
    Select ProjectId, ProjectIterationId    
        , Row_Number() Over( Partition By I1.ProjectId Order By MemberCount Desc ) As Rnk
    From CountByIteration
    )
Select  P.ProjectName, P.ProjectId, P.Status, P.StartDate
    , M.MemberId
    , M.FirstName + ' ' + M.LastName As ProjectManager
    , IterationMemberCounts.MemberCount
From Project As P
    Join RankedIterations As IterationMemberCounts
        On IterationMemberCounts.ProjectId = P.ProjectId
            And IterationMemberCounts.Rnk = 1
    Join ProjectIterationMembers As PIM
        On PIM.ProjectIterationId = IterationMemberCounts.ProjectIterationId
    Join Members As M
        On M.MemberId = PIM.MemberId
            And M.CompanyRole In( 'Project Manager', 'Admin' )

Now, it doesn't seem likely that you would want to arbitrarily choose the iteration with the highest member count. Instead, it would make more sense to choose the last iteration. To do that, we need a column that is safe to use to indicate sequence (i.e., not an identity column). If on the Iterations table there was a column called CreateDate, then we need only change the way in which the ranking is determined:

, Row_Number() Over( Partition By I1.ProjectId Order By CreateDate Desc ) As Rnk

Upvotes: 2

Samuel Neff
Samuel Neff

Reputation: 74909

You need to join the Iterations table twice, once inside the subquery and also on the outside, and join the inner and outer queries on ProjectID not ProjectIterationID.

If iterations cannot have separate project managers per iteration, then you can also refactor the outer Iterations join to remove the duplicate Project lines. The following will still give you duplicate Project lines, possibly with different Project Managers, but the MemberCount will be the same for each.

SELECT 
    DISTINCT(P.ProjectName), 
    P.ProjectID, 
    P.Status, 
    P.CompanyID, 
    P.StartDate,   
    M.MemberID,
    M.FirstName + ' ' + M.LastName AS ProjectManager, 
    (   SELECT  COUNT(PIM2.MemberID) 
        FROM    ProjectIterationMember PIM2,
                Iterations I2
        WHERE   I2.ProjectID = P.ProjectID
          AND   PIM2.PIM.ProjectIterationID = I2.ProjectIterationID
    ) AS MemberCount 
FROM 
   Project P, 
   Member M, 
   ProjectIterationMember PIM, 
   Iterations I
WHERE 
        M.CompanyRole IN ('Project Manager', 'Admin') 
    AND P.CompanyID = '" + co_id + "'
    AND M.MemberID = PIM.MemberID 
    AND I.ProjectIterationID = PIM.ProjectIterationID 
    AND I.ProjectID = P.ProjectID 
ORDER BY 
    P.StartDate DESC

Upvotes: 2

Related Questions