Hi Team,
Could you please help or guide us with the below analytics query? The use case is when the user is onboarded into Saviynt. Then, when user gets its region , the particular region group should assigned accordingly I have written a query in the data analyser it is showing as data not found. I have some users who satisfy the criteria
SELECT
ev.entitlement_valuekey AS entvaluekey,
a.accountkey AS acctKey,
a.name AS accName,
u.userKey,
'Provision Access' AS Default_Action_For_Analytics
FROM
users u
JOIN user_accounts ua ON ua.userkey = u.userkey
JOIN accounts a ON a.accountkey = ua.accountkey
JOIN account_entitlements1 ae ON ae.accountkey = a.accountkey
JOIN entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
JOIN (
SELECT DISTINCT et.entitlementtypekey, et.endpointkey
FROM entitlement_types et
WHERE et.endpointkey = 1157
) et ON ev.entitlementtypekey = et.entitlementtypekey
WHERE
(
CASE WHEN u.DEPARTMENTNAME = 'HR'
AND ev.entitlement_valuekey = '783145' THEN 1 ELSE 0 END
) = 1
AND et.endpointkey = 1157
AND ev.entitlement_valuekey = '783145'
AND a.accountkey IN (
SELECT
accountkey
FROM
accounts
WHERE
endpointkey = 1157
)
AND (
SELECT
1
FROM
user_accounts ua_inner
WHERE
ua_inner.userkey = u.userkey
AND ua_inner.accountkey = a.accountkey
AND ua_inner.accountkey NOT IN (
SELECT
ua_inner.accountkey
FROM
users u
JOIN user_accounts ua_inner ON ua_inner.userkey = u.userkey
JOIN accounts a_inner ON a_inner.accountkey = ua_inner.accountkey
JOIN account_entitlements1 ae_inner ON ae_inner.accountkey = a_inner.accountkey
JOIN entitlement_values ev_inner ON ae_inner.entitlement_valuekey = ev_inner.entitlement_valuekey
JOIN (
SELECT DISTINCT et_inner.entitlementtypekey, et_inner.endpointkey
FROM entitlement_types et_inner
WHERE et_inner.endpointkey = 1157
) et_inner ON ev_inner.entitlementtypekey = et_inner.entitlementtypekey
WHERE
et_inner.endpointkey = 1157
)
);
