Need a help in Query to check whether Task is completed by other user or not before showing on dashboard
SELECT  A1.*, 
        CASE WHEN A2.taskId IS NULL THEN 'Task Completed' 
        ELSE 'open' END AS CurrentStatus
FROM AssignedTo A1
LEFT JOIN (
    SELECT taskId
    FROM AssignedTo 
    WHERE status IN ('open', 'close')
    GROUP BY taskId
    HAVING COUNT(DISTINCT status) = 1
    ) A2 ON A2.taskId = A1.taskId
ORDER BY A1.taskId;


Fiddler: http://rextester.com/CAHOPS39734

Help from this post



The above query will return one extra column as CurrentStatus. If the taskId has any close status, for those tasks the CurrentStatus will be Task Completed and for remaining tasks it will show as open only.

So the output will be:

id  taskId  userId  status  CurrentStatus
1   1       1       open    open
4   1       4       open    open
2   1       2       open    open
3   1       3       open    open
5   2       1       open    Task Completed
6   2       2       close   Task Completed
7   3       1       open    open
8   3       2       open    open