I have two tables below:

ChallengeTask: tells how many tasks that a challenge has.

TaskCompletion: tells which task user has completed.

The idea is that for each user, mark a challenge as completed if and only if all the tasks inside that challenge have been completed.

From the table above:

1. challengeId: 4B0CD7D0 and 5F686235 have only one taskId: EED7AC7A
2. challengeId: A6A43CFD has 2 tasks: EED7AC7A and 77BEC343
3. User 1, 2, 3 have completed the only task in their challenge. So, challenge needs to marked as completed
4. User 4 has only completed one task in the challenge. So, challenge must not be marked as completed

So the end result table should look like this:

I've been trying to build a stored proc that will return me the result table above, but have not got any successful yet. The issue I have is that as soon as I have completed one task, it would mark my challenge successful, it does not check the second one.

This is what I have so far:

select distinct cu.UserId, cu.ChallengeId, 
        case when cu.CompletedDate is not null then 1 else 0 end as Completed 
    from ChallengeUser cu
    inner join ChallengeTask ct ON cu.ChallengeId = ct.ChallengeId

Here is the commands to populate data:

DROP TABLE IF EXISTS ChallengeTask
GO

DROP TABLE IF EXISTS TaskCompletion
GO

DROP TABLE IF EXISTS ChallengeUser
GO

CREATE TABLE ChallengeTask
(
    ChallengeId UNIQUEIDENTIFIER,
    TaskId UNIQUEIDENTIFIER
)
GO

CREATE TABLE TaskCompletion
(
    UserId INT,
    ChallengeId UNIQUEIDENTIFIER,
    TaskId UNIQUEIDENTIFIER,
    CompletedDate DATETIMEOFFSET DEFAULT NULL
)
GO

CREATE TABLE ChallengeUser
(
    UserId INT,
    ChallengeId UNIQUEIDENTIFIER,
    CompletedDate DATETIMEOFFSET DEFAULT NULL
)
GO

INSERT INTO ChallengeTask VALUES('4B0CD7D0-3C16-452B-A14B-7F952E2372D6', 'EED7AC7A-45EE-4965-A262-526B010B24C8')
GO

INSERT INTO ChallengeTask VALUES('5F686235-31CE-4F4F-8A19-CB2BBC213908', 'EED7AC7A-45EE-4965-A262-526B010B24C8')
GO

INSERT INTO ChallengeTask VALUES('F3B4C545-6BC5-4350-8D47-00109CE91A62', 'EED7AC7A-45EE-4965-A262-526B010B24C8')
GO

INSERT INTO ChallengeTask VALUES('F3B4C545-6BC5-4350-8D47-00109CE91A62', '9D50BCFA-4573-4D56-A9AB-045CE320AB2B')
GO

INSERT INTO TaskCompletion VALUES(1, '4B0CD7D0-3C16-452B-A14B-7F952E2372D6', 'EED7AC7A-45EE-4965-A262-526B010B24C8', SYSDATETIMEOFFSET())
GO

INSERT INTO TaskCompletion VALUES(2, '4B0CD7D0-3C16-452B-A14B-7F952E2372D6', 'EED7AC7A-45EE-4965-A262-526B010B24C8', SYSDATETIMEOFFSET())
GO

INSERT INTO TaskCompletion VALUES(3, '4B0CD7D0-3C16-452B-A14B-7F952E2372D6', 'EED7AC7A-45EE-4965-A262-526B010B24C8', SYSDATETIMEOFFSET())
GO

INSERT INTO TaskCompletion VALUES(4, 'F3B4C545-6BC5-4350-8D47-00109CE91A62', 'EED7AC7A-45EE-4965-A262-526B010B24C8', SYSDATETIMEOFFSET())
GO

INSERT INTO TaskCompletion VALUES(4, 'F3B4C545-6BC5-4350-8D47-00109CE91A62', '9D50BCFA-4573-4D56-A9AB-045CE320AB2B', NULL)
GO

I would be really appreciated if someone can help me. Thank you.

CodePudding user response：

Compare a count of all tasks to a count of completed tasks.

SELECT cu.UserId, cu.ChallengeId 
    , CASE WHEN COUNT(*) = SUM(CASE WHEN cu.CompletedDate IS NOT NULL THEN 1 ELSE 0 END) THEN 1 ELSE 0 END AS Completed 
FROM TaskCompletion cu
INNER JOIN ChallengeTask ct ON cu.ChallengeId = ct.ChallengeId
GROUP BY cu.UserId, cu.ChallengeId
ORDER BY cu.UserId, cu.ChallengeId;