CODEWITHSUNDEEP
sqlsql-server
user474901
user474901

Reputation:

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

I want to create Sql script to see how many task open or on-hold only for logged in user who assigned to task result HelloWorld1(0) HelloWorld2(2) HelloWorld3(1) HelloWorld4(5)

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'
   AND Tasks.status = 'Onhold'
 GROUP BY Users_projects.userID,
          Projects.projectName,
          Projects.projectID,
          Users_Tasks.userID

enter image description here

Upvotes: 0

Views: 63

Answers (3)

Yaroslav
Yaroslav

Reputation: 6544

As already commented, the AND clause can not be satisfied as Task.Status can be only either Open or OnHold. Use the following 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

Upvotes: 0

Andomar
Andomar

Reputation: 238246

No row will be able to match:

(Tasks.status = 'Open' AND Tasks.status = 'Onhold')

Try

(Tasks.status = 'Open' OR Tasks.status = 'Onhold')

Or:

Tasks.status in ('Open', 'Onhold')

Upvotes: 0

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32720

You have:

AND (Tasks.status = 'Open' AND Tasks.status = 'Onhold')

Which can't be true; a Task can't have both values at the same time. You want:

AND (Tasks.status = 'Open' OR Tasks.status = 'Onhold')

Better yet, use IN:

AND (Tasks.status IN ('Open', 'Onhold') )

Upvotes: 1

Related Questions