05/22/2023 04:28 AM
Hi Team,
Users manager email is stored in CP35 of Hewittna account. Now we wanted to find the users whose managers email does not match with CP35.
Could you please help me prepare expression to get the users manager so that I can match it with CP35 from users Hewittna account.
I have below query not sure if its correct but I need-
--------------------------------
select u.username, u.displayname as userDisplayname, m.displayname as ManagerDisplayname, m.email, a.customproperty35, e.displayname as EndPointName from users u, users m, accounts a, user_accounts ua, endpoints e where u.manager=m.userkey and m.userkey=ua.userkey and ua.accountkey=a.accountkey and a.endpointkey=e.endpointkey and u.manager!=a.customproperty35 and e.endpointkey='433'
--------------------------------
So with bold items in above query can you please help me to get below values-
- Users manager display name
-User manager email
Thanks,
Amit Aware
05/22/2023 08:57 AM
Hi @SriRanga
Can you check below query:
select u.username, u.displayname as userDisplayname, m.displayname as ManagerDisplayname, m.email, a.customproperty35, e.displayname as EndPointName
from users u, users m, accounts a, user_accounts ua, endpoints e
where u.manager=m.userkey
and u.userkey=ua.userkey
and ua.accountkey=a.accountkey
and a.endpointkey=e.endpointkey
and m.email!=a.customproperty35
and a.endpointkey='433'
Thanks
05/22/2023 09:23 AM
Hi
Can you try with the below query
SELECT
u.username,
u.displayname AS userDisplayname,
m.displayname AS ManagerDisplayname,
m.email,
a.customproperty35,
e.displayname AS EndPointName
FROM
users u
JOIN users m ON u.manager = m.userkey
JOIN user_accounts ua ON m.userkey = ua.userkey
JOIN accounts a ON ua.accountkey = a.accountkey
JOIN endpoints e ON a.endpointkey = e.endpointkey
WHERE
m.email != a.customproperty35
AND e.endpointkey = '433'
05/22/2023 02:25 PM
If user doesn’t have manager it will not list user
05/23/2023 01:49 AM