Click HERE to see how Saviynt Intelligence is transforming the industry. |
05/14/2024 02:51 PM
Hi Team
I have the query to find out inactive delegates(below query):
SELECT u.username AS DELEGATEUSER, u.DISPLAYNAME AS DELEGATEUSERNAME, u.statuskey, d.CREATEDATE, monthname(d.CREATEDATE) as CreatedMonth, d.STARTDATE, d.ENDDATE FROM delegates d, users u WHERE u.userkey = d.DELEGATEUSERKEY AND u.STATUSKEY = 0;
but i need some help to write an analytics query to find list of active users having inactive delegates. Please assist.
Regards
Sangita Ladi
Solved! Go to Solution.
05/14/2024 04:57 PM - edited 05/14/2024 07:07 PM
SELECT 'Inactive User Delegation' AS CATEGORY, U.USERNAME AS 'DELEGATE USER', U.FIRSTNAME AS 'DELEGATE FIRST NAME', U.LASTNAME AS 'DELEGATE LAST NAME', U.EMAIL AS 'DELEGATE USER EMAIL', CASE U.STATUSKEY WHEN 1 then 'Active' when 0 then 'Inactive' else 'Blank' end as 'DELEGATE USER STATUS', D.CREATEDATE AS 'DELEGATION CREATED ON', D.STARTDATE AS 'DELEGATION START DATE', D.ENDDATE AS 'DELEGATION END DATE' FROM DELEGATES D, USERS U WHERE U.USERKEY = D.DELEGATEUSERKEY AND U.STATUSKEY =0 AND DATE(D.ENDDATE) >= DATE(NOW())
05/15/2024 07:52 AM - edited 05/15/2024 07:53 AM
Hi @sangitaladi ,
Below is the query to fetch active parent users with inactive delegate users (columns added)
SELECT
'Inactive User Delegation' AS CATEGORY,
PU.username as 'PARENT USERNAME',
PU.email as 'PARENT USER EMAIL',
PU.FIRSTNAME AS 'PARENT FIRST NAME',
PU.LASTNAME AS 'PARENT LAST NAME',
DU.USERNAME AS 'DELEGATE USER',
DU.FIRSTNAME AS 'DELEGATE FIRST NAME',
DU.LASTNAME AS 'DELEGATE LAST NAME',
DU.EMAIL AS 'DELEGATE USER EMAIL',
CASE
DU.STATUSKEY WHEN 1 then 'Active' when 0 then 'Inactive' else 'Blank'
end as 'DELEGATE USER STATUS',
D.CREATEDATE AS 'DELEGATION CREATED ON',
D.STARTDATE AS 'DELEGATION START DATE',
D.ENDDATE AS 'DELEGATION END DATE'
FROM
DELEGATES D, USERS DU , USERS PU
WHERE
DU.USERKEY = D.DELEGATEUSERKEY
AND D.USERKEY = PU.USERKEY
AND PU.STATUSKEY=1
AND DU.STATUSKEY=0
If this answers your question, please consider selecting Accept As Solutions and hit Kudos
05/15/2024 01:25 PM
this works. Thank you Prem