Last post Dec 09, 2020 02:45 PM by imapsp
Dec 08, 2020 02:03 PM|vahid.ch|LINK
I have a Table as below:
how can I write a query to get the result :
Dec 08, 2020 05:53 PM|imapsp|LINK
Why is the result 1 for Test1/cp1 and 0 for Test2/cp2? What is the criteria?
Dec 09, 2020 02:29 AM|yij sun|LINK
Accroding to your description,I don't understand your requirment clearly.
What is HasItAceess? Which column is group by? I'm guessing that you need to distinct the username.
Just like this:
select DISTINCT UserName,CompanyName from GT
If this isn't match your requirment,you could post more details to us.
Dec 09, 2020 07:56 AM|vahid.ch|LINK
thanks for the feedback.
actually I want to bring those users who have access to IT role, it doesn't matter if they have access to another role.
if they have access to IT role, then I want to set a flag in my query with true and if they don't have access to IT role, so accordingly the result will be false.
something like this:
CASE WHEN Group='IT'
THEN 1 ELSE 0 END AS HasITAccess from ...
in this case the result will be:
so, I have to remove the second row because Test 1 has access to It role and my requirement will be :
Dec 09, 2020 10:01 AM|vahid.ch|LINK
I found the solution:
SELECT Username,CompanyName,MAX(HasITAccess) FROM(
SELECT DISTINCT Username,CompanyName,
CASE WHEN GroupName='IT'
THEN 1 ELSE 0 END AS HasITAccess
FROM [Table]) AS result
GROUP BY Username,CompanyName
Dec 09, 2020 02:45 PM|imapsp|LINK
You can leave your query as follows:
MAX(CASE WHEN GroupName = 'IT' THEN 1 ELSE 0 END) AS HasITAccess
Hope this help