02/16/2023 10:06 AM
Our team is looking to filter out users with outstanding certifications (i.e users with no response in their certification) and launch a new campaign that will escalate to their manager to complete. Currently our query is using the cus.certfied is null and searching for null/no responses which works in Step 1 of the User Manger Campaign (employment Verification) . However, if there is a manager for example that completes the first step of selecting a status for their direct report but does not complete the second step (Access approval/revoke) the query will not filter out that user. Is there a query that can look for no response in both step 1 and step 2 of the user Manager campaign and filter out a user that has no response in either step?
current query :
username in (select cu.username from campaign cm inner join certification c on cm.id=c.campaignkey inner join certification_user_status cus on c.certkey=cus.certkey inner join users u on c.certifier=u.userkey inner join certification_user cu on cus.cert_userkey=cu.cert_userkey where cus.certified is null and cm.campaign_name='SelfCertify_ Testing')
02/16/2023 05:17 PM
Validate below query
username in (select distinct cu.username from campaign cm inner join certification c on cm.id=c.campaignkey inner join certification_user_status cus on c.certkey=cus.certkey inner join users u on c.certifier=u.userkey inner join certification_user cu on cus.cert_userkey=cu.cert_userkey where cus.certified is null and cm.campaign_name IN ('<Campaign_Name>')
UNION
SELECT distinct u2.username AS username FROM certification c INNER JOIN campaign ca ON c.CAMPAIGNKEY = ca.id INNER JOIN certification_account_entitlement1_status cs ON c.certkey = cs.certkey INNER JOIN certification_account caa ON caa.cert_accountkey = cs.cert_accountkey INNER JOIN user_accounts ua ON ua.accountkey = caa.accountkey INNER JOIN users u2 ON u2.userkey = ua.userkey WHERE cs.CERTIFIED is null and ca.campaign_name IN ('<Campaign_Name>'))
02/16/2023 05:21 PM
Sine the Step2 includes information of Account and Entitlements, you can use the below 2 tables in your query to check for the same.
Table: certification_user_account_status
COLUMN: CERTIFIED
Table: certification_account_entitlement1_status
COLUMN: CERTIFIED