Campaign Query Report

[ARNAB86]

Hi All,

Can someone provide me the query to fetch the Campaign details including all the certification details in last 30 days details

I need the below columns to be present there in the query

1. Certification ID

2.  Campaign Name

3. Campaign Status

4. Certification Name

5.Certification Status

6. Certifier Name

7. Campaign Start Date

8. Campaign End Date

[Raghu · Accepted solution]

@ARNAB86  Please find below article for get query form based on your requirement

https://forums.saviynt.com/t5/saviynt-knowledge-base/campaign-report-queries/ta-p/50171

https://forums.saviynt.com/t5/identity-governance/um-campaign-data-analytics-report/m-p/54339?attach...

[rushikeshvartak · Accepted solution]

SELECT ce.certkey      AS 'Certification ID',
       c.campaign_name AS 'campaign name',
       CASE c.status
         WHEN 1 THEN 'In Progress'
         WHEN 2 THEN 'Completed'
         WHEN 5 THEN 'In Progress'
         WHEN 4 THEN 'Preview'
         WHEN 6 THEN 'Discontinued'
         WHEN 7 THEN 'Expired'
         WHEN 8 THEN 'Launching'
       END             AS 'Campaign Status',
       ce.cert_name    AS 'Certification Name',
       CASE
         WHEN ce.status = 0 THEN 'New'
         WHEN ce.status = 1 THEN 'In Progress'
         WHEN ce.status = 2 THEN 'Completed'
         WHEN ce.status = 3 THEN 'Locked'
         WHEN ce.status = 4 THEN 'Expired'
         WHEN ce.status = 7 THEN 'Discontinued'
         WHEN ce.status = 8 THEN 'Locked and Task Created'
         ELSE NULL
       END             AS 'Certification Status',
       u.displayname   AS 'Certifier Name',
       c.start_date    AS 'Campaign Start Date',
       c.end_date      AS 'Campaign End Date'
FROM   campaign c,
       certification ce,
       users u
WHERE  c.start_date >= Date_sub(Curdate(), interval 30 day)
       AND c.id = ce.campaignkey
       AND ce.certifier = u.userkey 

[ARNAB86]

Thanks All

[rushikeshvartak]

Please click the 'Accept As Solution' button on the reply that answered your original question.