Difference between revisions of "Sample Queries"

Revision as of 11:08, 31 July 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>