cancel
Showing results for 
Search instead for 
Did you mean: 

Monitored IDs by Department Logic

admin_laura
Level 4

I am searching for the tables used to create the Monitored IDs by Department.  The objective is to create an exception report identifying persons who are not assigned to a department.  My IT folks believe they can create a solution but have requested souce details for Monitored IDs by Department

Thank you.

2 REPLIES 2

EV_Ajay
Level 6
Employee Accredited

Hi admin laura,

Please run following SQL query against CA Customer Database and you will get desire result.

 

Select tc.CaseID 'DepartmentID',tc.Name 'Department Name',tu.EmployeeID,ta.Address 'EmailID'

From tblAddressUser tu

JOIN tblHistCaseAddressUser th

ON th.AddressOwnerID=tu.AddressOwnerID

JOIN tblCase tc

ON tc.CaseID=th.CaseID AND th.EndDate IS NULL JOIN tblAddress ta ON ta.AddressOwnerID=tu.AddressOwnerID

Where tc.Type=102 AND tu.MonitorStatusID=120 Order by tc.Name

Query Result.jpg

EV_Ajay
Level 6
Employee Accredited

Hi admin laura,

Have you tried above script ?

Is there anything i can help you ?