Sample Queries

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

<source lang="sql">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</source>

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. <source lang="sql">SELECT prob_ctg.persid, prob_ctg.sym, prob_ctg.description FROM [dbo].[prob_ctg] WHERE (prob_ctg.del = 0)</source>

TOP 5 Incident Areas

This query will return TOP 5 used categories against which Incidents are logged <source lang="sql">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</source>

Duplicate Contacts

<source lang="sql">SELECT ca_contact.userid, Count(ca_contact.userid) AS NumOccurrences FROM ca_contact GROUP BY ca_contact.userid HAVING (((Count(ca_contact.userid))>1));</source>

Duplicate Configuration Items (Assets)

<source lang="sql">SELECT resource_name, count(resource_name)[How_many_times] FROM ca_owned_resource GROUP BY resource_name HAVING count(resource_name) > 1;</source>

SQL Query Reference for Contact Types

-- Select all Contact Types (ca_contact_type)

<source lang="sql">SELECT id, name FROM dbo.ca_contact_type</source>

-- Select all Groups from (ca_contacts)

<source lang="sql">SELECT contact_uuid, last_name, contact_type FROM ca_contact WHERE contact_type = 2308</source>

-- Select all Analysts from (ca_contacts)

<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2307</source>

-- Select all Customers and Employees from (ca_contacts)

<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2305 or contact_type = 2310</source>

-- Select all NULL contact_type(ca_contacts)

<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type is NULL</source>

-- Select all Help Desk from (ca_contacts)

<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2306</source>

-- Select all Managers from (ca_contacts)

<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2301</source>

-- Select all Operators from (ca_contacts)

<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2300</source>

-- Select all Technician from (ca_contacts)

<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2304</source>

-- Select all Users from (ca_contacts)

<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2302</source>

-- Select all Vendors from (ca_contacts)

<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact WHERE contact_type = 2303</source>

SQL Query for Date Fields Conversion

-- Select open date and closed date for all closed tickes from (call_req)

<source = lang="sql"> 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' </source>

-- Select open date, convert it to normal time and back to UNIX (USD) time

<source = lang="sql"> 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' </source>

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'))

<source = lang="sql">

   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

</source>