Difference between revisions of "Sample Queries"

Revision as of 14:54, 10 April 2008

SQL Query for Request Area

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

SELECT prob_ctg.persid, prob_ctg.sym, prob_ctg.description FROM [dbo].[prob_ctg] WHERE (prob_ctg.del = 0)

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

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

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