Use Case
This document provides consolidated list of some of the frequently used Campaign Reports based on the state of the Campaign/Certification. This can be lifted and shifted at any customer environment.
This Document will cover queries to create report for the following scenarios:
- Campaign Not Expired
- Certifications Completed
- Certification Discontinued
- Certification In Progress
- Certifications Expired
Pre-requisites
N/A
Applicable Version(s)
AllSolution
The query to fetch the report of Campaigns Not Expired.
select cm.campaign_name as 'Campaign Name',u.SYSTEMUSERNAME as 'Campaign Owner',cm.start_date as 'Campaign Start Date',cm.end_date as 'Campaign End Date'from campaign cm,users u where cm.campaign_owner_id=u.USERKEY and cm.status=5 and now() > cm.end_date
|
Certifications Completed:
The query to fetch the report of Completed Certifications. This report will show all the Completed Certifications for all the campaigns.
select certkey as CertificationId, ca.campaign_name as CampaignName, CASE WHEN ca.status=1 THEN 'New' WHEN ca.status=2 THEN 'Completed' WHEN ca.status=3 THEN 'Cancelled' WHEN ca.status=4 THEN 'Preview' WHEN ca.status=5 THEN 'In Progress' WHEN ca.status=6 THEN 'Discontinued' WHEN ca.status=7 THEN 'Expired' WHEN ca.status=8 THEN 'Launching' END as CampaignStatus, CERT_NAME as CertificationName, 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=5 THEN 'Discontinued' WHEN c.status=6 THEN 'Preview' WHEN c.status=7 THEN 'Discontinued' WHEN c.status=8 THEN 'LockedandTaskCreated' WHEN c.status=9 THEN 'Cancelled' END as CertificationStatus, concat(u.lastname,', ',u.firstname) as CertifierName, DATE_FORMAT(c.STARTDATE, '%Y-%m-%d') as CampaignStart,DATE_FORMAT(c.enddate, '%Y-%m-%d') as CampaignEnd from certification c, users u, campaign ca where u.userkey=c.certifier and c.CAMPAIGNKEY = ca.id and c.status in (3,8,2,10) and ca.status in (1,2,5,7); |
Certification Discontinued:
The query to fetch the report of Discontinued Certifications.
select certkey as CertificationId, ca.campaign_name as CampaignName, CASE WHEN ca.status=1 THEN 'New' WHEN ca.status=2 THEN 'Completed' WHEN ca.status=3 THEN 'Cancelled' WHEN ca.status=4 THEN 'Preview' WHEN ca.status=5 THEN 'In Progress' WHEN ca.status=6 THEN 'Discontinued' WHEN ca.status=7 THEN 'Expired' WHEN ca.status=8 THEN 'Launching' END as CampaignStatus, CERT_NAME as CertificationName, 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=5 THEN 'Discontinued' WHEN c.status=6 THEN 'Preview' WHEN c.status=7 THEN 'Discontinued' WHEN c.status=8 THEN 'LockedandTaskCreated' WHEN c.status=9 THEN 'Cancelled' END as CertificationStatus, concat(u.lastname,', ',u.firstname) as CertifierName, DATE_FORMAT(c.STARTDATE, '%Y-%m-%d') as CampaignStart,DATE_FORMAT(c.enddate, '%Y-%m-%d') as CampaignEnd from certification c, users u, campaign ca where u.userkey=c.certifier and c.CAMPAIGNKEY = ca.id and c.status in (5,7); |
Certification In Progress:
The query to fetch the report of Certifications In Progress.
select certkey as CertificationId, ca.campaign_name as CampaignName, CASE WHEN ca.status=1 THEN 'New' WHEN ca.status=2 THEN 'Completed' WHEN ca.status=3 THEN 'Cancelled' WHEN ca.status=4 THEN 'Preview' WHEN ca.status=5 THEN 'In Progress' WHEN ca.status=6 THEN 'Discontinued' WHEN ca.status=7 THEN 'Expired' WHEN ca.status=8 THEN 'Launching' END as CampaignStatus, CERT_NAME as CertificationName, 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=5 THEN 'Discontinued' WHEN c.status=6 THEN 'Preview' WHEN c.status=7 THEN 'Discontinued' WHEN c.status=8 THEN 'LockedandTaskCreated' WHEN c.status=9 THEN 'Cancelled' END as CertificationStatus, concat(u.lastname,', ',u.firstname) as CertifierName, DATE_FORMAT(c.STARTDATE, '%Y-%m-%d') as CampaignStart,DATE_FORMAT(c.enddate, '%Y-%m-%d') as CampaignEnd from certification c, users u, campaign ca where u.userkey=c.certifier and c.CAMPAIGNKEY = ca.id and c.status in (0,1) and ca.status in (1,5);
|
Certifications Expired:
The query to fetch the report of Expired Certifications.
select certkey as CertificationId, ca.campaign_name as CampaignName, CASE WHEN ca.status=1 THEN 'New' WHEN ca.status=2 THEN 'Completed' WHEN ca.status=3 THEN 'Cancelled' WHEN ca.status=4 THEN 'Preview' WHEN ca.status=5 THEN 'In Progress' WHEN ca.status=6 THEN 'Discontinued' WHEN ca.status=7 THEN 'Expired' WHEN ca.status=8 THEN 'Launching' END as CampaignStatus, CERT_NAME as CertificationName, 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=5 THEN 'Discontinued' WHEN c.status=6 THEN 'Preview' WHEN c.status=7 THEN 'Discontinued' WHEN c.status=8 THEN 'LockedandTaskCreated' WHEN c.status=9 THEN 'Cancelled' END as CertificationStatus, concat(u.lastname,', ',u.firstname) as CertifierName, DATE_FORMAT(c.STARTDATE, '%Y-%m-%d') as CampaignStart,DATE_FORMAT(c.enddate, '%Y-%m-%d') as CampaignEnd from certification c, users u, campaign ca where u.userkey=c.certifier and c.CAMPAIGNKEY = ca.id and c.status in (4); |
References
