SQL - Users JML Report
SELECT U.USERNAME,
U.FIRSTNAME,
U.LASTNAME,
U.EMPLOYEETYPE,
U.CUSTOMPROPERTY9 AS 'JOINER DAT',
U.CUSTOMPROPERTY8 AS 'MOVER DAT',
U.CUSTOMPROPERTY10 AS 'LOA START DAT',
U.CUSTOMPROPERTY11 AS 'TRANSFER DAT',
U.CUSTOMPROPERTY12 AS 'TERMINATION EVENT DAT' ,
U.CUSTOMPROPERTY13 AS 'TERMINATION DAT',
U.CUSTOMPROPERTY17 AS 'Corporate ID',
U.OWNER AS SUPERVISOR,
MANAGER.EMAIL AS 'MANAGER EMAIL',
CASE
WHEN U.STATUSKEY = 0
AND Isnull(U.MANAGER) = 0
AND MANAGER.STATUSKEY = 0 THEN 'Inactive User with Inactive Manager'
WHEN U.STATUSKEY = 0
AND Isnull(U.MANAGER) = 0
AND MANAGER.STATUSKEY = 1 THEN 'Inactive User with Active Manager'
WHEN U.STATUSKEY = 0
AND Isnull(U.MANAGER) = 1 THEN 'Inactive User with No Manager'
WHEN U.STATUSKEY = 0
AND Isnull(U.MANAGER) = 0
AND MANAGER.STATUSKEY IS NULL THEN 'Inactive User with Blank Status Manager '
WHEN U.STATUSKEY = 1
AND Isnull(U.MANAGER) = 0
AND MANAGER.STATUSKEY = 0 THEN 'Active User with Inactive Manager'
WHEN U.STATUSKEY = 1
AND Isnull(U.MANAGER) = 0
AND MANAGER.STATUSKEY = 1 THEN 'Active User with Active Manager'
WHEN U.STATUSKEY = 1
AND Isnull(U.MANAGER) = 1 THEN 'Active User with No Manager'
WHEN U.STATUSKEY = 1
AND Isnull(U.MANAGER) = 0
AND MANAGER.STATUSKEY IS NULL THEN 'Active User with Blank Status Manager'
WHEN U.STATUSKEY IS NULL
AND Isnull(U.MANAGER) = 0
AND MANAGER.STATUSKEY = 0 THEN 'Blank Status User with Inactive Manager'
WHEN U.STATUSKEY IS NULL
AND Isnull(U.MANAGER) = 0
AND MANAGER.STATUSKEY = 1 THEN 'Blank Status User with Active Manager'
WHEN U.STATUSKEY IS NULL
AND Isnull(U.MANAGER) = 1 THEN 'Blank Status User with No Manager'
WHEN U.STATUSKEY IS NULL
AND Isnull(U.MANAGER) = 0
AND MANAGER.STATUSKEY IS NULL THEN 'Blank Status User with Blank Status Manager'
ELSE 'Blank'
END 'USER MANAGER CATEGORY' ,
USEROWNER.USERNAME AS 'USER OWNER ON TERMINATE NAME',
CASE
WHEN U.STATUSKEY = 1 THEN 'Active'
WHEN U.STATUSKEY = 0 THEN 'Inactive'
ELSE 'Blank'
END AS 'USER STATE',
U.DISPLAYNAME AS 'PREFERRED NAME',
U.CITY,
U.STATE,
U.COUNTRY,
U.EMAIL,
U.LOCATION,
U.EMPLOYEEID,
U.REGION,
U.DEPARTMENTNAME,
U.COSTCENTER,
U.JOBCODE,
U.JOBCODEDESC AS 'FLEX STRING',
U.TITLE,
U.COMPANYNAME,
U.ENTITY AS 'DIVISION',
U.CUSTOMPROPERTY1 AS 'LEGAL FIRST NAME',
U.CUSTOMPROPERTY2 AS 'LEGAL LAST NAME',
U.CUSTOMPROPERTY3 AS 'VENDOR NAME',
U.CUSTOMPROPERTY4 AS 'CAREER LEVEL CODE',
U.CUSTOMPROPERTY5 AS 'CAREER LEVEL DESCRIPTION',
U.CUSTOMPROPERTY6 AS 'EMPLOYEMENT STATUS',
U.CUSTOMPROPERTY7 AS 'ACQUISITION',
U.CUSTOMPROPERTY14 AS 'IS TERMINATED',
U.CUSTOMPROPERTY16 AS 'MANAGER AZUER ACCOUNT ID',
U.CUSTOMPROPERTY20 AS 'MANAGERS MANAGER EMAIL',
U.CUSTOMPROPERTY21 AS 'OWNER ON TRANSFER EMAIL',
U.STREET AS 'ADDRESS',
U.JOB_FUNCTION AS 'COST CENTER NAME',
U.JOBDESCRIPTION AS 'JOB FAMILY',
U.CUSTOMPROPERTY64 AS 'MOVER FLAG',
U.CUSTOMPROPERTY65 AS 'RULE EVALUATION FLAG',
U.SAVUPDATEDATE AS 'USER UPDATE DAT',
MANAGER.USERNAME AS 'MANAGER',
MANAGER.FIRSTNAME AS 'MANAGER FIRST NAME',
MANAGER.LASTNAME AS 'MANAGER LAST NAME',
CASE
WHEN MANAGER.STATUSKEY = 0 THEN 'Inactive'
WHEN MANAGER.STATUSKEY = 1 THEN 'Active'
ELSE 'Blank'
END 'MANAGER STATUS',
MANAGER.SAVUPDATEDATE AS 'MANAGER UPDATE DAT'
FROM USERS U
LEFT JOIN USERS MANAGER
ON U.MANAGER = MANAGER.USERKEY
LEFT JOIN USERS USEROWNER
ON U.OWNERONTERMINATE = USEROWNER.USERKEY
WHERE (
CASE
WHEN Find_in_set('Active' , '${Select_User_Status}') THEN U.STATUSKEY = 1
END
OR
CASE
WHEN Find_in_set('Inactive' , '${Select_User_Status}') THEN U.STATUSKEY = 0
END
OR
CASE
WHEN Find_in_set('Blank' , '${Select_User_Status}') THEN U.STATUSKEY IS NULL
END )
AND (
CASE
WHEN '${Select_User_Lifecycle_Event}'='Joiner' THEN ( U.CUSTOMPROPERTY9 >= '${Select_User_Lifecycle_Event_Date_From}'
AND U.CUSTOMPROPERTY9 <= '${Select_User_Lifecycle_Event_Date_To}' )
WHEN '${Select_User_Lifecycle_Event}'='Transfer' THEN ( U.CUSTOMPROPERTY11 >= '${Select_User_Lifecycle_Event_Date_From}'
AND U.CUSTOMPROPERTY11 <= '${Select_User_Lifecycle_Event_Date_To}' )
WHEN '${Select_User_Lifecycle_Event}'='Termination' THEN ( U.CUSTOMPROPERTY13 >= '${Select_User_Lifecycle_Event_Date_From}'
AND U.CUSTOMPROPERTY13 <= '${Select_User_Lifecycle_Event_Date_To}' )
END )
AND (
CASE
WHEN Find_in_set('All' , '${Input_Multiple_Managers_Workday_IDs}') THEN (1 = 1)
END
OR
CASE
WHEN '${Input_Multiple_Managers_Workday_IDs}' NOT LIKE '%All%' THEN (U.OWNER IN ( '${Input_Multiple_Managers_Workday_IDs.replace(",","',
'")}' ) )
END )
Comments
Post a Comment