CODEWITHSUNDEEP
sql-server
sujan
sujan

Reputation: 1

How to use conditional in SQL statement

What I am trying to accomplish is Group column.

If a task has multiple users working on it, then I want to label as Team else individual.

Task    Sub-Task    User    Group
1234    9999             A  Team
1234    9998             B  Team
1234    9997             C  Team

2345    6666             A  Team
2345    6665             B  Team

3456    5555             A  Individual A
4567    4444             B  Individual B
5678    3333             C  Individual C

Upvotes: 0

Views: 67

Answers (5)

Eilert Hjelmeseth
Eilert Hjelmeseth

Reputation: 618

Window functions are a great way to handle this. The tricky part with using the COUNT window function is it does not allow you to do COUNT(DISTINCT ...). So if a user has 2 subtasks it'll return 2 when I'm guessing you'd want it to return 1.

However, we could use MIN and MAX window functions to see if there is only one user for a task.

MIN([user]) OVER (PARTITION BY task) will get the "minimum" user value per task in the result set. Likewise, MAX will get the "maximum". If they're the same that means there is only one user for that task, even if the same user has multiple subtasks.

SELECT
    task,
    subtask,
    [user],
    CASE
        WHEN MIN([user]) OVER (PARTITION BY task) = MAX([user]) OVER (PARTITION BY task)
        THEN 'Individual ' + [user]
        ELSE 'Team'
    END
FROM Tasks

Without window functions, this would be an appropriate way to do it also, which is similar to other answers, but I thought I'd include it here, noting that it only counts distinct users.

SELECT
    A.task,
    A.subtask,
    A.[user],
    CASE
        WHEN B.user_count = 1
        THEN 'Individual ' + [user]
        ELSE 'Team'
    END
FROM Tasks A 
INNER JOIN 
    (
        SELECT 
            task,
            COUNT(DISTINCT [user]) user_count
        FROM #tmp
        GROUP BY
            task
    ) B 
ON      A.task = B.task

Since you're planning to run this against a decently large table perhaps testing both solutions for performance might be helpful.

Upvotes: 0

Zack
Zack

Reputation: 2341

Another option is to set up a derived table (either as a CTE or inline) that counts the number of users per task. You can then use this to show the group you need (pretty similar to @Squirrel's answer, just replacing the window function with a derived record set).

WITH
    TasksGroupedByUser(Task, UserCount) AS
    (
        SELECT Task, COUNT(DISTINCT [User])
        FROM Tasks
        GROUP BY Task
    )
SELECT
    Tasks.*,
    CASE
        WHEN TasksGroupedByUser.UserCount > 1 THEN 'Group'
        ELSE 'Individual' + ' ' + Tasks.[User]
    END
FROM Tasks
    INNER JOIN TasksGroupedByUser ON
        TasksGroupedByUser.Task = Tasks.Task

Upvotes: 0

user5151179
user5151179

Reputation: 585

My 2 cents:

CREATE TABLE #tmp
(
  [Id] BIGINT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  [Task] INT,
  [SubTask] INT,
  [User] NCHAR(1),
  [Group] NVARCHAR(32)
)

INSERT INTO #tmp ([Task], [SubTask], [User], [Group]) VALUES (1234, 9999, 'A', 'Team');
INSERT INTO #tmp ([Task], [SubTask], [User], [Group]) VALUES (1234, 9998, 'B', 'Team');
INSERT INTO #tmp ([Task], [SubTask], [User], [Group]) VALUES (1234, 9997, 'C', 'Team');
INSERT INTO #tmp ([Task], [SubTask], [User], [Group]) VALUES (2345, 6666, 'A', 'Team');
INSERT INTO #tmp ([Task], [SubTask], [User], [Group]) VALUES (2345, 6665, 'B', 'Team');
INSERT INTO #tmp ([Task], [SubTask], [User], [Group]) VALUES (3456, 5555, 'A', 'Individual A');
INSERT INTO #tmp ([Task], [SubTask], [User], [Group]) VALUES (4567, 4444, 'B', 'Individual B');
INSERT INTO #tmp ([Task], [SubTask], [User], [Group]) VALUES (5678, 3333, 'C', 'Individual C');

SELECT          [A].[Task],
                [B].[SubTask],
                [B].[User],
                CASE [A].[Cnt]
                WHEN 1 THEN 'Individual ' + [User]
                ELSE 'Team'
                END AS [GroupCalculated],
                [Group],
                [A].[Cnt]

FROM (          SELECT          [Task],
                                COUNT(*) AS [Cnt]
                FROM            #tmp
                GROUP BY        [Task]) AS [A]

INNER JOIN      #Tmp AS [B]
    ON          [B].[Task] = [A].[Task];

DROP TABLE #tmp;

Upvotes: 0

Skin
Skin

Reputation: 11197

Does this do what you want?

SELECT Task, SubTask, [User], [Group] = CASE WHEN GroupCount > 1 THEN 'Group' ELSE 'Individual' END
FROM (
    SELECT *, [GroupCount] = (SELECT COUNT(*) FROM TestTable AS B WHERE B.Task = A.Task)
    FROM TestTable AS A) AS C

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

You can make use of window function OVER(). When the COUNT() is more than 1 Team else Individual

select  *,
        case when count(User) over (partition by Task) > 1
             then 'Team' 
             else 'Individual' 
             end
from    yourtable

Upvotes: 1

Related Questions