Difference between revisions of "Sample Queries"

Latest revision as of 07:18, 6 November 2008

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>