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
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
How a COntactId can be filtered based on above requirements, please suggest.
Thanks in advance
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
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.