Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Need support to write analytics SQL query to get active users with inactive delegates

sangitaladi
Regular Contributor
Regular Contributor

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

 

 

 

3 REPLIES 3

rushikeshvartak
All-Star
All-Star

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())


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

PremMahadikar
Valued Contributor
Valued Contributor

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

this works. Thank you Prem