Hi,
I am working on a runtime Analytics which needs to pull in the list of actions taken by certifiers for a specific campaign along with the corresponding details (similar to what we see when we drill into a campaign in UI)
1. Revoke > includes everyone that the certifier asked to be revoked.
2. Approve >Includes everyone that the certifier asked to be Approve.
2. Consult > include everyone that the certifier asked for consultation on
4. No Action > Whatever the certifier has not taken any action on
the corresponding data I am trying to pull is the campaign details, certification name and status, certifier name and details, manager details, entitlement and endpoints, account being reviewed and account holder`s details. Basically, an overall certification detailed report.
I am able to pull in all details successfully except the above 4 actions corresponding to each record.
Which column and table can I use for this ?
Below is the query I have used to get to this point:
select
T1.campaign_name AS 'Campaign',
T1.CERT_NAME AS 'Certification Name',
CASE WHEN T1.CampaignStatus = 1 THEN 'New' WHEN T1.CampaignStatus = 2 THEN 'Completed' WHEN T1.CampaignStatus = 3 THEN 'Cancelled' WHEN T1.CampaignStatus = 4 THEN 'Preview' WHEN T1.CampaignStatus = 5 THEN 'In Progress' WHEN T1.CampaignStatus = 6 THEN 'Discontinued' WHEN T1.CampaignStatus = 7 THEN 'Expired' WHEN T1.CampaignStatus = 8 THEN 'Launching' END AS 'Campaign Status',
CASE WHEN T1.CertificationStatus = 0 THEN 'New' WHEN T1.CertificationStatus = 1 THEN 'In Progress' WHEN T1.CertificationStatus = 2 THEN 'Completed' WHEN T1.CertificationStatus = 3 THEN 'Locked' WHEN T1.CertificationStatus = 4 THEN 'Expired' WHEN T1.CertificationStatus = 5 THEN 'Discontinued' WHEN T1.CertificationStatus = 6 THEN 'Preview' WHEN T1.CertificationStatus = 7 THEN 'Discontinued' WHEN T1.CertificationStatus = 8 THEN 'LockedandTASkCreated' WHEN T1.CertificationStatus = 9 THEN 'Cancelled' END AS 'Certification Status',
CASE WHEN ((u.FIRSTNAME is null OR u.FIRSTNAME = '')AND(u.lastname is null OR u.lastname = '')) THEN 'Not Available' ELSE CONCAT(u.FIRSTNAME,' ',u.lastname) END AS 'Certifier Name',
DATE_FORMAT(T1.STARTDATE, '%Y-%m-%d') AS 'Campaign Start Date',
DATE_FORMAT(T1.enddate, '%Y-%m-%d') AS 'Campaign End Date',
T1.ENDPOINTNAME AS 'Endpoint Name',
T1.EntitlementType AS 'Entitlement Type',
T1.Entitlement_Value as 'Entitlement Value',
T1.NAME AS 'Account Name',
CASE WHEN T1.Account_status = '1' THEN 'active' WHEN T1.Account_status = '2' THEN 'inactive' WHEN T1.Account_status = 3 THEN 'Decommission active' WHEN T1.Account_status = 4 THEN 'Decommission inactive' ELSE T1.Account_status END AS 'Account Status',
CASE WHEN ((u3.FIRSTNAME is null OR u3.FIRSTNAME = '')AND(u3.lastname is null OR u3.lastname = '')) THEN 'Not Available' ELSE CONCAT(u3.FIRSTNAME,' ',u3.lastname) END AS 'Account Holder(User)',
CASE WHEN ((u1.FIRSTNAME is null OR u1.FIRSTNAME = '')AND(u1.lastname is null OR u1.lastname = '')) THEN 'Not Available' ELSE CONCAT(u1.FIRSTNAME,' ',u1.lastname) END AS 'User`s Manager Name',
CASE WHEN u1.statuskey = 1 THEN 'ACTIVE'WHEN u1.statuskey = 0 THEN 'INACTIVE' ELSE 'Not Available' END AS 'User`s Manager Status'
FROM
(
SELECT
DISTINCT cm.campaign_name AS 'campaign_name',
c.CERT_NAME AS 'CERT_NAME',
c.certifier AS 'CertifierKey',
ca.accountkey AS 'CA_AccountKey',
a.ACCOUNTID AS 'ACCOUNTID',
a.NAME AS 'NAME',
a.status AS 'Account_status',
c.STARTDATE AS 'STARTDATE',
c.enddate AS 'enddate',
cm.status AS 'CampaignStatus',
c.status AS 'CertificationStatus',
ev.entitlement_value AS 'Entitlement_Value',
case WHEN ET.DISPLAYNAME is null THEN ET.ENTITLEMENTNAME WHEN ET.DISPLAYNAME ='' THEN ET.ENTITLEMENTNAME ELSE ET.DISPLAYNAME END AS 'EntitlementType',
case when EP.DISPLAYNAME is null THEN EP.ENDPOINTNAME ELSE EP.DISPLAYNAME END AS 'ENDPOINTNAME'
FROM
certification c,
campaign cm,
certification_account_entitlement1_status caes,
certification_account ca,
accounts a,
certification_entitlement_value cev,
entitlement_values ev,
entitlement_types ET,
endpoints EP
WHERE
ca.CERT_ACCOUNTKEY = caes.CERT_ACCOUNTKEY
AND ca.ACCOUNTKEY = a.ACCOUNTKEY
AND c.CERTKEY = caes.CERTKEY
AND ET.ENDPOINTKEY=EP.ENDPOINTKEY
AND cm.id = c.CAMPAIGNKEY
AND EV.entitlementtypekey=ET.entitlementtypekey
AND cev.CERT_ENTITLEMENT_VALUEKEY = caes.CERT_ENTITLEMENT_VALUEKEY
AND cev.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY
) AS T1
LEFT JOIN user_accounts ua ON T1.CA_AccountKey=ua.accountkey
LEFT JOIN users u ON T1.CertifierKey = u.userkey
LEFT JOIN users u3 ON ua.userkey = u3.userkey
LEFT JOIN users u2 ON u.MANAGER = u2.userkey
LEFT JOIN users u1 ON u3.MANAGER = u1.userkey
Note: I know the query isn't optimized at the moment, but we are prioritizing getting the required data first and then optimizing the above query.
Thanks,
Vivek Tangri
