Use Case
Analytical queries to verify the datasets post-launch for both User Manager and Entitlement Owner Campaigns.
Applicable Version(s)
All
Solution
*Add required Campaign Name as per your requirement.
- Number of entitlements per account per certification
select t.CAMPAIGN_NAME, CERT_NAME,t.ACCOUNT_NAME, t.Number_of_Entitlements from (
select distinct c.campaign_name,ct.CERT_NAME,ca.ACCOUNTKEY, ca.NAME as 'ACCOUNT_NAME', count(cv.ENTITLEMENT_VALUEKEY) as 'Number_of_Entitlements' from campaign c, certification ct, certification_account_entitlement1_status cae1, certification_account ca, certification_entitlement_value cv where c.id = ct.campaignkey and ct.CERTKEY = cae1.certkey and cae1.CERT_ACCOUNTKEY= ca.CERT_ACCOUNTKEY and cv.CERT_ENTITLEMENT_VALUEKEY= cae1.CERT_ENTITLEMENT_VALUEKEY and campaign_name='<<CAMPAIGN NAME>>' group by ca.ACCOUNTKEY,cert_name, campaign_name)t;
- Number of Entries per certification
select distinct c.campaign_name,ct.CERTKEY,ct.CERT_NAME , count(cae1.CERTKEY) as 'Number of Entries' from campaign c, certification ct, certification_account_entitlement1_status cae1 where c.id = ct.campaignkey and ct.CERTKEY = cae1.certkey and campaign_name ='<<CAMPAIGN NAME>>' group
by cert_name, campaign_name;
- Number of accounts per certification
select distinct c.campaign_name,ct.CERTKEY,ct.CERT_NAME , count(ca.ACCOUNTKEY) from campaign c, certification ct, certification_account_entitlement1_status cae1, certification_account ca where c.id = ct.campaignkey and ct.CERTKEY = cae1.certkey and campaign_name ='<<CAMPAIGN NAME>>' and ca.CERT_ACCOUNTKEY= cae1.CERT_ACCOUNTKEY group by cert_name, campaign_name;
References
N/A
