Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
02/15/2024 03:45 AM - edited 02/15/2024 03:46 AM
Hi,
I want to extract the details of an account which doesn't have any entitlements. I am providing entitlement_value as NULL but the query is returning no results even though there is a user with no entitlements. I am attaching the query for you reference.
SELECT U.username ,
U.customproperty3 ,
U.departmentnumber ,
U.jobdescription ,
U.startdate ,
U.DEPARTMENTNAME ,
A.name,
A.status ,
A.created_on ,
EV.entitlement_value,
et.entitlementname
FROM users U,
accounts A,
account_entitlements1 AE,
entitlement_values EV,
entitlement_types et
WHERE ( U.customproperty3 = 65
OR U.customproperty3 = 01 )
AND A.endpointkey = 5
AND U.statuskey = 1
AND A.STATUS=1
AND A.accountid = U.customproperty28
AND A.created_on >= DATE_SUB(CURDATE(), INTERVAL 100 DAY)
AND AE.entitlement_valuekey = EV.entitlement_valuekey
AND A.accountkey = AE.accountkey
AND et.entitlementtypekey = ev.entitlementtypekey
AND (et.entitlementname = "Role" or et.entitlementname = "Profile" )
AND EV.entitlement_value is NULL
Thanks and regards,
Soumik Das
02/15/2024 05:16 AM
Hi @soumik_das
If an account does not have any entitlements, it would have no entry in the account_entitlements1 table hence you can try the below query to fetch such users:
SELECT U.username ,
U.customproperty3 ,
U.departmentnumber ,
U.jobdescription ,
U.startdate ,
U.DEPARTMENTNAME ,
A.name,
A.status ,
A.created_on
FROM users U join user_accounts ua on U.userkey=ua.userkey join accounts a on ua.accountkey=a.accountkey where a.accountkey not in (select accountkey from account_entitlements1)
Database Schema Reference (saviyntcloud.com)
04/10/2024 03:35 AM - edited 04/10/2024 04:56 AM
Hi @armaanzahir ,
Please can you let me know for a particular entitlement type how can we get the account details where there is no values of that particular entitlement type?
Thanks and regards,
Soumik Das
04/10/2024 04:33 PM
Can you try this?
SELECT U.username ,
U.customproperty3 ,
U.departmentnumber ,
U.jobdescription ,
U.startdate ,
U.DEPARTMENTNAME ,
A.name,
A.status ,
A.created_on
FROM users U
join user_accounts ua
on U.userkey=ua.userkey
join accounts a
on ua.accountkey=a.accountkey
join endpoints e
on a.endpointkey=e.endpointkey
join entitlement_types et
on e.endpointkey=et.endpointkey
where a.accountkey not in (select accountkey from account_entitlements1)
and et.entitlementname='Name'
04/10/2024 06:45 PM
SELECT U.username,
U.customproperty3,
U.departmentnumber,
U.jobdescription,
U.startdate,
U.departmentname,
A.name,
A.status,
A.created_on,
EV.entitlement_value,
et.entitlementname
FROM users U
join accounts A
ON A.accountid = U.customproperty28
left join account_entitlements1 AE
ON A.accountkey = AE.accountkey
left join entitlement_values EV
ON AE.entitlement_valuekey = EV.entitlement_valuekey
left join entitlement_types et
ON EV.entitlementtypekey = et.entitlementtypekey
WHERE ( U.customproperty3 = 65
OR U.customproperty3 = 01 )
AND A.endpointkey = 5
AND U.statuskey = 1
AND A.status = 1
AND ( et.entitlementname = "role"
OR et.entitlementname = "profile" )
AND EV.entitlement_value IS NULL
AND A.created_on >= Date_sub(Curdate(), interval 100 day);