TMACUL

http://www.servicedeskusers.com/Sample_Queries.

Blog Post created by TMACUL Champion on Jan 1, 2016

Sample Queries

From SDU

Jump to: navigation, search

 

To make corrections or additions to this article, select the edit tab above.
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

Retrieved from "http://www.servicedeskusers.com/Sample_Queries"

This page was last modified 07:18, 6 November 2008.  This page has been accessed 10,681 times.  Content is available under Attribution-Noncommercial-Share Alike 3.0 Unported.   Disclaimers         

Outcomes