locked
Returning flags based on conditions RRS feed

  • Question

  • Hi,

    I have a user table where a single user can have multiple roles across country but will have a unique role for a particular city.

    UserID  Email City Role
    1 ABC city 1 developer
    2 XYZ city2 seller
    3 ABC city3 seller
    4 ABC city2 Buyer
    5 ABC city4 Broker
    6 PQR city2 seller
    7 DEF city4 buyer
    8 XYZ city4 Broker
    9 XYZ city3 Buyer
    10 PQR city3 developer
           


    Now, once I pass the email,  I need four flags for each role(Buyer,seller,broker,developer) if user has any of these roles in any city and a fifth flag IsHavingMultipleRoles if the user has more than one role.

    For example, for ABC I need the output as 

    IsBuyer IsSeller IsBroker IsDeveloper IsHavingMultipleRoles 
    1 1 1 1 1

    For DEF, I need the output as

    IsBuyer IsSeller IsBroker IsDeveloper IsHavingMultipleRoles 
    1 0 0 0 0

    What would be the best way to achieve this?

    Thanks in advance.

    Thursday, June 28, 2018 5:35 AM

Answers

  • you can do it like this

    DECLARE @Email varchar(100) = 'ABC'
    
    
    SELECT Email,
    MAX(CASE WHEN Role = 'Buyer' THEN 1 ELSE 0 END) AS IsBuyer,
    MAX(CASE WHEN Role = 'Seller' THEN 1 ELSE 0 END) AS IsSeller,
    MAX(CASE WHEN Role = 'Broker' THEN 1 ELSE 0 END) AS IsBroker,
    MAX(CASE WHEN Role = 'Developer' THEN 1 ELSE 0 END) AS IsDeveloper,
    CASE WHEN COUNT(DISTINCT Role) > 1 THEN 1 ELSE 0 END AS IsHavingMultipleRoles
    FROM Table
    WHERE Email = @Email
    GROUP BY Email


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ABC30 Thursday, June 28, 2018 6:56 AM
    Thursday, June 28, 2018 5:40 AM
  • Thanks Vishakh for the quick reply.

    But I missed one scenario. I have a fifth Role "Customer". But in above scenario if DEF is having Customer role, then the IsHavingMultipleRoles should still return 0.

    With the above query, COUNT(DISTINCT Role) > 1 i is counting that role also.

    See this?

    create table RoleRow
    (
     UserID int,
     Email varchar(30),
     City varchar(64),
     [Role] varchar(64)
    )
    
    insert into RoleRow values
    (1,'ABC','city1','developer'), 
    (2,'XYZ','city2','seller'), 
    (3,'ABC','city3','seller'), 
    (4,'ABC','city2','Buyer'), 
    (5,'ABC','city4','Broker'), 
    (6,'PQR','city2','seller'), 
    (7,'DEF','city4','buyer'), 
    (8,'XYZ','city4','Broker'), 
    (9,'XYZ','city3','Buyer'), 
    (10,'PQR','city3','developer'),
    (11,'DEF','city5','Customer')
    
    DECLARE @Email varchar(100) = 'DEF'
    
    
    SELECT Email,
    MAX(CASE WHEN Role = 'Buyer' THEN 1 ELSE 0 END) AS IsBuyer,
    MAX(CASE WHEN Role = 'Seller' THEN 1 ELSE 0 END) AS IsSeller,
    MAX(CASE WHEN Role = 'Broker' THEN 1 ELSE 0 END) AS IsBroker,
    MAX(CASE WHEN Role = 'Developer' THEN 1 ELSE 0 END) AS IsDeveloper,
    CASE WHEN COUNT(DISTINCT CASE WHEN [Role]='Customer' AND Email='DEF' THEN NULL ELSE [Role] END) > 1 THEN 1 ELSE 0 END AS IsHavingMultipleRoles
    FROM RoleRow
    WHERE Email = @Email
    GROUP BY Email

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ABC30 Thursday, June 28, 2018 8:18 AM
    Thursday, June 28, 2018 7:03 AM
  • Thanks Vishakh for the quick reply.

    But I missed one scenario. I have a fifth Role "Customer". But in above scenario if DEF is having Customer role, then the IsHavingMultipleRoles should still return 0.

    With the above query, COUNT(DISTINCT Role) > 1 i is counting that role also.

    just make this small tweak

    DECLARE @Email varchar(100) = 'ABC'
    
    
    SELECT Email,
    MAX(CASE WHEN Role = 'Buyer' THEN 1 ELSE 0 END) AS IsBuyer,
    MAX(CASE WHEN Role = 'Seller' THEN 1 ELSE 0 END) AS IsSeller,
    MAX(CASE WHEN Role = 'Broker' THEN 1 ELSE 0 END) AS IsBroker,
    MAX(CASE WHEN Role = 'Developer' THEN 1 ELSE 0 END) AS IsDeveloper,
    CASE WHEN COUNT(DISTINCT CASE WHEN Role <> 'Customer' THEN Role END) > 1 THEN 1 ELSE 0 END AS IsHavingMultipleRoles
    FROM Table
    WHERE Email = @Email
    GROUP BY Email


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ABC30 Thursday, June 28, 2018 8:18 AM
    Thursday, June 28, 2018 7:05 AM

All replies

  • you can do it like this

    DECLARE @Email varchar(100) = 'ABC'
    
    
    SELECT Email,
    MAX(CASE WHEN Role = 'Buyer' THEN 1 ELSE 0 END) AS IsBuyer,
    MAX(CASE WHEN Role = 'Seller' THEN 1 ELSE 0 END) AS IsSeller,
    MAX(CASE WHEN Role = 'Broker' THEN 1 ELSE 0 END) AS IsBroker,
    MAX(CASE WHEN Role = 'Developer' THEN 1 ELSE 0 END) AS IsDeveloper,
    CASE WHEN COUNT(DISTINCT Role) > 1 THEN 1 ELSE 0 END AS IsHavingMultipleRoles
    FROM Table
    WHERE Email = @Email
    GROUP BY Email


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ABC30 Thursday, June 28, 2018 6:56 AM
    Thursday, June 28, 2018 5:40 AM
  • Thanks Vishakh for the quick reply.

    But I missed one scenario. I have a fifth Role "Customer". But in above scenario if DEF is having Customer role, then the IsHavingMultipleRoles should still return 0.

    With the above query, COUNT(DISTINCT Role) > 1 i is counting that role also.

    Thursday, June 28, 2018 6:15 AM
  • Thanks Vishakh for the quick reply.

    But I missed one scenario. I have a fifth Role "Customer". But in above scenario if DEF is having Customer role, then the IsHavingMultipleRoles should still return 0.

    With the above query, COUNT(DISTINCT Role) > 1 i is counting that role also.

    See this?

    create table RoleRow
    (
     UserID int,
     Email varchar(30),
     City varchar(64),
     [Role] varchar(64)
    )
    
    insert into RoleRow values
    (1,'ABC','city1','developer'), 
    (2,'XYZ','city2','seller'), 
    (3,'ABC','city3','seller'), 
    (4,'ABC','city2','Buyer'), 
    (5,'ABC','city4','Broker'), 
    (6,'PQR','city2','seller'), 
    (7,'DEF','city4','buyer'), 
    (8,'XYZ','city4','Broker'), 
    (9,'XYZ','city3','Buyer'), 
    (10,'PQR','city3','developer'),
    (11,'DEF','city5','Customer')
    
    DECLARE @Email varchar(100) = 'DEF'
    
    
    SELECT Email,
    MAX(CASE WHEN Role = 'Buyer' THEN 1 ELSE 0 END) AS IsBuyer,
    MAX(CASE WHEN Role = 'Seller' THEN 1 ELSE 0 END) AS IsSeller,
    MAX(CASE WHEN Role = 'Broker' THEN 1 ELSE 0 END) AS IsBroker,
    MAX(CASE WHEN Role = 'Developer' THEN 1 ELSE 0 END) AS IsDeveloper,
    CASE WHEN COUNT(DISTINCT CASE WHEN [Role]='Customer' AND Email='DEF' THEN NULL ELSE [Role] END) > 1 THEN 1 ELSE 0 END AS IsHavingMultipleRoles
    FROM RoleRow
    WHERE Email = @Email
    GROUP BY Email

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ABC30 Thursday, June 28, 2018 8:18 AM
    Thursday, June 28, 2018 7:03 AM
  • Thanks Vishakh for the quick reply.

    But I missed one scenario. I have a fifth Role "Customer". But in above scenario if DEF is having Customer role, then the IsHavingMultipleRoles should still return 0.

    With the above query, COUNT(DISTINCT Role) > 1 i is counting that role also.

    just make this small tweak

    DECLARE @Email varchar(100) = 'ABC'
    
    
    SELECT Email,
    MAX(CASE WHEN Role = 'Buyer' THEN 1 ELSE 0 END) AS IsBuyer,
    MAX(CASE WHEN Role = 'Seller' THEN 1 ELSE 0 END) AS IsSeller,
    MAX(CASE WHEN Role = 'Broker' THEN 1 ELSE 0 END) AS IsBroker,
    MAX(CASE WHEN Role = 'Developer' THEN 1 ELSE 0 END) AS IsDeveloper,
    CASE WHEN COUNT(DISTINCT CASE WHEN Role <> 'Customer' THEN Role END) > 1 THEN 1 ELSE 0 END AS IsHavingMultipleRoles
    FROM Table
    WHERE Email = @Email
    GROUP BY Email


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by ABC30 Thursday, June 28, 2018 8:18 AM
    Thursday, June 28, 2018 7:05 AM