SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2010
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post count how many tasks under project is open or onhold for logged in user

    I want to created nav menu to show Project name and number of task is open or onhold for associated logged in tech
    the result should be like this my problem with is my script give me total number of task under project
    for tech1 and his associate task are open or onhold
    hello World 1(0)
    hello World 2(3)
    hello World 3(1)
    hello World 4(2)

    Code:
    SELECT Projects.projectID,
        Projects.projectName + ' ' + '(' + CONVERT(NVARCHAR, COUNT(Projects_tasks.taskID)) + ')' AS Project
      FROM Projects
      LEFT JOIN Projects_tasks ON Projects.projectID = Projects_tasks.projectID
     INNER JOIN Users_projects ON Projects.projectID = Users_projects.projectID
     INNER JOIN Tasks ON Projects_tasks.taskID = Tasks.taskID
     INNER JOIN Users_Tasks ON Tasks.taskID = Users_Tasks.TaskID
     WHERE Users_Tasks.userID = @userID
       AND (Tasks.status = 'Open' OR Tasks.status = 'Onhold')
     --also this code can be used: 
     --AND Tasks.status in ('Open', 'Onhold')
     GROUP BY Users_projects.userID,
              Projects.projectName,
              Projects.projectID,
              Users_Tasks.userID
    Capture.PNG

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,079
    Mentioned
    53 Post(s)
    Tagged
    0 Thread(s)
    All your joins should be inner. There's no need to do a conversion on something that will be counted. Other than that I see nothing else wrong?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •