Click HERE to see how Saviynt Intelligence is transforming the industry. |
04/30/2024 01:34 AM
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
Solved! Go to Solution.
04/30/2024 02:16 AM
@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
04/30/2024 09:44 PM
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
05/01/2024 07:12 PM
Thanks All
05/01/2024 07:45 PM
Please click the 'Accept As Solution' button on the reply that answered your original question.