Sample Queries
From SDU
To discuss or ask questions about this article, select the discussion tab above.
Examples
Groups with no Active Members
SELECT b.last_name, b.middle_name, b.first_name, b.userid, actbool.sym [User_Status], a.last_name [Group_Name] FROM grpmem INNER JOIN ca_contact a ON grpmem.group_id = a.contact_uuid INNER JOIN ca_contact b ON grpmem.member = b.contact_uuid INNER JOIN actbool ON b.inactive = actbool.enum WHERE actbool.sym = 'Inactive' ORDER BY a.last_name
Incident/Problem/Request Areas
This query will return a list of persistent ID's, Symbols (Request Areas) and Descriptions from SQL. Only ACTIVE Request Areas will be displayed by this query.
SELECT prob_ctg.persid, prob_ctg.sym, prob_ctg.description FROM [dbo].[prob_ctg] WHERE (prob_ctg.del = 0)
TOP 5 Incident Areas
This query will return TOP 5 used categories against which Incidents are logged
SELECT TOP 5 count(category)[Incidents_Logged], sym [Category_Used] FROM call_req INNER JOIN prob_ctg ON call_req.category = prob_ctg.persid WHERE type = 'I' GROUP BY sym ORDER BY Incidents_Logged DESC
Duplicate Contacts
SELECT ca_contact.userid, Count(ca_contact.userid) AS NumOccurrences FROM ca_contact GROUP BY ca_contact.userid HAVING (((Count(ca_contact.userid))>1));
Duplicate Configuration Items (Assets)
SELECT resource_name, count(resource_name)[How_many_times] FROM ca_owned_resource GROUP BY resource_name HAVING count(resource_name) > 1;
SQL Query Reference for Contact Types
-- Select all Contact Types (ca_contact_type)
SELECT id, name FROM dbo.ca_contact_type
-- Select all Groups from (ca_contacts)
SELECT contact_uuid, last_name, contact_type FROM ca_contact WHERE contact_type = 2308
-- Select all Analysts from (ca_contacts)
SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2307
-- Select all Customers and Employees from (ca_contacts)
SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2305 OR contact_type = 2310
-- Select all NULL contact_type(ca_contacts)
SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type IS NULL
-- Select all Help Desk from (ca_contacts)
SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2306
-- Select all Managers from (ca_contacts)
SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2301
-- Select all Operators from (ca_contacts)
SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2300
-- Select all Technician from (ca_contacts)
SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2304
-- Select all Users from (ca_contacts)
SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2302
-- Select all Vendors from (ca_contacts)
SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2303
SQL Query for Date Fields Conversion
-- Select open date and closed date for all closed tickes from (call_req)
SELECT DateAdd(ss, call_req.open_date, '19700101') AS 'Open_Date', DateAdd(ss, call_req.close_date, '19700101') AS 'Close_Date' FROM call_req WHERE call_req.STATUS = 'CL'
-- Select open date, convert it to normal time and back to UNIX (USD) time
DECLARE @standard_datetime DateTime SELECT @standard_datetime = DateAdd(ss,call_req.open_date,'19700101') FROM call_req WHERE ref_num = '309' SELECT open_date AS [USD_OR_UNIX_TIME], DateAdd(ss,call_req.open_date,'19700101') AS [NORMAL_TIME], DateDiff(ss,'19700101',@standard_datetime) AS NORMAL_TIME_TO_UNIX FROM call_req WHERE ref_num = '309'
Survey Results and comments query by CR Resolved Date
--Date can be changed by changing in the date in the sceond part of the query (TRUNC(TO_DATE('2008/11/05','YYYY/MM/DD'))
SELECT s.sym AS " Survey name" ,(cr.resolve_date + 7200)/86400 + TO_DATE('1970/01/01','YYYY/MM/DD') resolve_date ,sq.txt AS "Survey Question" ,sa.txt AS "Survey Awnsers" ,sa.selected AS "Awnser Selected" ,cr.ref_num ,cc.first_name ,cc.last_name ,sq.qcomment AS "User Comment" FROM survey s, survey_question sq, survey_answer sa, call_req cr, ca_contact cc WHERE (cr.resolve_date + 7200)/86400 + TO_DATE('1970/01/01','YYYY/MM/DD') >= TRUNC(TO_DATE('2008/11/05','YYYY/MM/DD')) AND (cr.resolve_date + 7200-86400)/86400 + TO_DATE('1970/01/01','YYYY/MM/DD') < TRUNC(TO_DATE('2008/11/05','YYYY/MM/DD')) AND s.id = sq.owning_survey AND s.last_mod_by = cc.contact_uuid AND cr.id = s.object_id AND sq.id = sa.own_srvy_question AND sa.selected IS NOT NULL ORDER BY 1
