Sample Queries

SQL Query for Request Area

To obtain the most current listing, from the SQL Query Analyzer run the following query on the MDB.

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

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.

SQL Query for TOP 5 Categories

This query will return TOP 5 used categories againt 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>

SQL Query for 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>

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>

---

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.