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

[sangitaladi]

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

[rushikeshvartak]

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

[PremMahadikar · Accepted solution]

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

[sangitaladi]

this works. Thank you Prem