CA SDM::.  VERIFY inactive analyst for more that 90 days

Document created by TMACUL Champion on Feb 16, 2017
Version 1Show Document
  • View in full screen mode

VERIFY inactive analyst for more that 90 days

 

USE mdb
SELECT ca_contact.contact_type AS contact_type, ca_contact.inactive AS inactive, ca_contact.userid, acctyp_v2.sym AS access_type, acctyp_v2.licensed
FROM ca_contact LEFT OUTER JOIN usp_contact ON ca_contact.contact_uuid = usp_contact.contact_uuid LEFT OUTER JOIN acctyp_v2 ON usp_contact.c_acctyp_id = acctyp_v2.id
WHERE ca_contact.contact_uuid NOT IN (select contact from session_log where login_time > (DATEDIFF(s, '1970/01/01 00:00:00', GETDATE())-7776000))
AND ca_contact.inactive=0 and acctyp_v2.licensed=1
AND (ca_contact.contact_type > 2305 AND ca_contact.contact_type != 2308)
AND (acctyp_v2.id != 2402 AND acctyp_v2.id != 10002)


Set Analyst to employee if inactive more that 90 days

 

USE mdb
UPDATE usp_contact SET c_acctyp_id=2405 where contact_uuid IN
(SELECT ca_contact.contact_uuid AS contact
FROM ca_contact
LEFT OUTER JOIN usp_contact ON ca_contact.contact_uuid = usp_contact.contact_uuid
LEFT OUTER JOIN acctyp_v2 ON usp_contact.c_acctyp_id = acctyp_v2.id
WHERE ca_contact.contact_uuid NOT IN
(select contact from session_log where login_time > (DATEDIFF(s, '1970/01/01 00:00:00', GETDATE())-7776000))
AND ca_contact.inactive=0
AND acctyp_v2.licensed=1
AND (ca_contact.contact_type > 2305 AND ca_contact.contact_type != 2308)
AND (acctyp_v2.id != 2402 AND acctyp_v2.id != 10002)
)

 

By Jerom Mayer 

 

Summary CA SDM  MS SQL Querys - TSQL 

1 person found this helpful

Attachments

    Outcomes