Sample
SELECT
DISTINCT c.CERT_NAME AS 'Certification Name',
ca.campaign_name AS 'Campaign Name',
users.username AS Certifier,
CASE WHEN c.STATUS = 0 THEN 'New' WHEN c.STATUS = 1 THEN 'In Progress' WHEN c.STATUS = 2 THEN 'Completed' WHEN c.STATUS = 3 THEN 'Locked' WHEN c.STATUS = 4 THEN 'Expired' WHEN c.STATUS = 6 THEN 'Preview' WHEN c.STATUS = 7 THEN 'Discontinued' WHEN c.STATUS = 8 THEN 'Locked and Task Created' WHEN c.STATUS = 10 THEN 'FULLY EXECUTED' ELSE c.STATUS END AS 'Status of Certification',
c.CREATEDATE AS 'CERT_START_DATE',
c.ENDDATE AS 'CERT_END_DATE',
endpoints.displayname AS Application,
cev.Entitlement_Value,
et.ENTITLEMENTNAME,
CASE WHEN cs.CERTIFIED = 0 THEN 'REVOKED' WHEN cs.CERTIFIED = 1 THEN 'CERTIFIED' WHEN cs.CERTIFIED = 2 THEN 'CERTIFIED' WHEN cs.CERTIFIED = 3 THEN 'REVOKED' WHEN cs.CERTIFIED = 5 THEN 'CONDITIONALLY CERTIFIED' WHEN cs.CERTIFIED = -1 THEN 'TERMINATED' ELSE 'NO RESPONSE' END AS FinalAction,
cs.AUDIT_TRAIL AS 'ACCOUNT_ENTITLEMENT_AUDIT_LOG',
'' as 'ENTITLEMENT_AUDIT_LOG'
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 certification_entitlement_value cev ON cev.cert_entitlement_valuekey = cs.cert_entitlement_valuekey
INNER JOIN users ON users.userkey = c.certifier
INNER JOIN user_accounts ua ON ua.accountkey = caa.accountkey
INNER JOIN users u2 ON u2.userkey = ua.userkey
INNER JOIN endpoints ON endpoints.ENDPOINTKEY = caa.endpointkey
INNER JOIN entitlement_types et ON cev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY
WHERE
c.status in (0, 1)
and ca.campaign_type=1
union all
SELECT
DISTINCT c.CERT_NAME AS 'Certification Name',
ca.campaign_name AS 'Campaign Name',
users.username AS Certifier,
CASE WHEN c.STATUS = 0 THEN 'New' WHEN c.STATUS = 1 THEN 'In Progress' WHEN c.STATUS = 2 THEN 'Completed' WHEN c.STATUS = 3 THEN 'Locked' WHEN c.STATUS = 4 THEN 'Expired' WHEN c.STATUS = 6 THEN 'Preview' WHEN c.STATUS = 7 THEN 'Discontinued' WHEN c.STATUS = 8 THEN 'Locked and Task Created' WHEN c.STATUS = 10 THEN 'FULLY EXECUTED' ELSE c.STATUS END AS 'Status of Certification',
c.CREATEDATE AS 'CERT_START_DATE',
c.ENDDATE AS 'CERT_END_DATE',
endpoints.displayname AS Application,
cev.ENTITLEMENT_VALUE,
entitlement_types.ENTITLEMENTNAME,
CASE WHEN cvv.CERTIFIED = 1 THEN 'CERTIFIED' WHEN cvv.CERTIFIED is null then 'NO RESPONSE' ELSE 'DOES NOT BELONG TO ME' END AS FinalAction,
'' AS 'ACCOUNT_ENTITLEMENT_AUDIT_LOG',
cvv.AUDIT_TRAIL as 'ENTITLEMENT_AUDIT_LOG'
FROM
certification c
INNER JOIN campaign ca ON c.CAMPAIGNKEY = ca.id
INNER JOIN users on users.userkey = c.CERTIFIER
INNER JOIN cert_ent_values_status cvv on cvv.CERTKEY = c.CERTKEY
INNER JOIN certification_entitlement_value cev on cvv.CERT_ENTITLEMENT_VALUEKEY = cev.CERT_ENTITLEMENT_VALUEKEY
INNER JOIN entitlement_types ON cev.ENTITLEMENTTYPEKEY = entitlement_types.ENTITLEMENTTYPEKEY
INNER JOIN endpoints ON endpoints.ENDPOINTKEY = entitlement_types.endpointkey
WHERE
cvv.CERT_ENTITLEMENT_VALUEKEY = cev.CERT_ENTITLEMENT_VALUEKEY
and c.status in (0,1)
Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.
