Conditionally data extract from SQL table

PANDEY Prashant 145 Reputation points
2023-03-13T16:25:18.27+00:00

Hello Experts,

Please help to solve below business case..

I have a SQL 'Contact' table having ContactId as GUID and ContactType columns.

A Contact can have one or more then 1 contact types, these types are of User/Super User/Bespoke/Remove Access etc.

some sample data is shown below

AD529B74-E1B8-ED11-A81B-000D3A660B07 718960009(Recognised Organisation User)

2676ED09-E6B8-ED11-A81B-000D3A660B07 718960009(Recognised Organisation User)

183C6766-0DF2-EC11-BB3D-000D3A666558 718960002(Bespoke)

183C6766-0DF2-EC11-BB3D-000D3A666558 718960004(Super User)

183C6766-0DF2-EC11-BB3D-000D3A666558 718960006(User)

183C6766-0DF2-EC11-BB3D-000D3A666558 718960009(Recognised Organisation User)

C4798A24-4F8E-EC11-B400-000D3ABA1F5C 718960006(User)

C4798A24-4F8E-EC11-B400-000D3ABA1F5C 718960007(Grant User)

C4798A24-4F8E-EC11-B400-000D3ABA1F5C 718960009(Recognised Organisation User)

now the ask is to filter contact based on below criteria.

  • If contact is just setup as 'Recognised Organisation User'  .
    • Action A
  • If contact is setup as 'Recognised Organisation User'  AND any other role .
    • Action B

How a COntactId can be filtered based on above requirements, please suggest.

Thanks in advance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,013 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,593 questions
0 comments No comments
{count} votes

Accepted answer
  1. Naomi Nosonovsky 7,886 Reputation points
    2023-03-13T16:56:42.4466667+00:00

    select ContactID, sum(case when ContactType = 'Recognised Organisation User' then 1 else 0 end) as ROUOnly, count(*) as AllTypes

    from Contacts

    group by ContactID

    Then use logic for ROUOnly = AllTypes vs ROUOnly < AllTypes


1 additional answer

Sort by: Most helpful
  1. LiHongMSFT-4306 28,121 Reputation points
    2023-03-14T02:58:37.7166667+00:00

    Hi @PANDEY Prashant

    Please check this:

    --If contact is just setup as 'Recognised Organisation User' 
    SELECT ContactId
    FROM Contact A
    WHERE ContactType = 'Recognised Organisation User' AND NOT EXISTS(SELECT * FROM Contact WHERE ContactId=A.ContactId AND ContactType<>A.ContactType)
    
    --If contact is setup as 'Recognised Organisation User'  AND any other role
    SELECT ContactId
    FROM Contact A
    WHERE ContactType = 'Recognised Organisation User' AND EXISTS(SELECT * FROM Contact WHERE ContactId=A.ContactId AND ContactType<>A.ContactType)
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.