Difference between revisions of "Sample Queries"

From SDU
Jump to: navigation, search
m
m
Line 6: Line 6:
 
''To obtain the most current listing, from the SQL Query Analyzer run the following query on the MDB.''  
 
''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)'''
+
<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.''
 
''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.''
Line 15: Line 17:
 
This query will return TOP 5 used categories againt which Incidents are logged
 
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  
+
<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  
 
INNER JOIN prob_ctg ON call_req.category = prob_ctg.persid  
 
WHERE type = 'I'  
 
WHERE type = 'I'  
 
GROUP BY sym  
 
GROUP BY sym  
−
ORDER BY Incidents_Logged desc'''
+
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>
  
  
Line 27: Line 36:
 
''-- Select all Contact Types (ca_contact_type)''
 
''-- Select all Contact Types (ca_contact_type)''
  
−
'''SELECT id, name FROM dbo.ca_contact_type'''
+
<source lang="sql">SELECT id, name
 +
FROM dbo.ca_contact_type</source>
  
 
''-- Select all Groups from (ca_contacts) ''
 
''-- Select all Groups from (ca_contacts) ''
  
−
'''SELECT contact_uuid, last_name, contact_type FROM ca_contact
+
<source lang="sql">SELECT contact_uuid, last_name, contact_type
−
WHERE contact_type = 2308'''
+
FROM ca_contact
 +
WHERE contact_type = 2308</source>
  
 
''-- Select all Analysts from (ca_contacts)''
 
''-- Select all Analysts from (ca_contacts)''
  
−
'''SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact
+
<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type
−
WHERE contact_type = 2307'''
+
FROM ca_contact
 +
WHERE contact_type = 2307</source>
  
 
''-- Select all Customers and Employees from (ca_contacts)''
 
''-- Select all Customers and Employees from (ca_contacts)''
  
−
'''SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact
+
<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type
−
WHERE contact_type = 2305 or contact_type = 2310'''
+
FROM ca_contact
 +
WHERE contact_type = 2305 or contact_type = 2310</source>
  
 
''-- Select all NULL contact_type(ca_contacts)''
 
''-- Select all NULL contact_type(ca_contacts)''
  
−
'''SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact
+
<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type
−
WHERE contact_type is NULL'''
+
FROM ca_contact
 +
WHERE contact_type is NULL</source>
  
 
''-- Select all Help Desk from (ca_contacts)''
 
''-- Select all Help Desk from (ca_contacts)''
  
−
'''SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact
+
<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type
−
WHERE contact_type = 2306'''
+
FROM ca_contact
 +
WHERE contact_type = 2306</source>
  
 
''-- Select all Managers from (ca_contacts)''
 
''-- Select all Managers from (ca_contacts)''
  
−
'''SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact
+
<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type
−
WHERE contact_type = 2301'''
+
FROM ca_contact
 +
WHERE contact_type = 2301</source>
  
 
''-- Select all Operators from (ca_contacts)''
 
''-- Select all Operators from (ca_contacts)''
  
−
'''SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact
+
<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type
−
WHERE contact_type = 2300'''
+
FROM ca_contact
 +
WHERE contact_type = 2300</source>
  
 
''-- Select all Technician from (ca_contacts)''
 
''-- Select all Technician from (ca_contacts)''
  
−
'''SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact
+
<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type
−
WHERE contact_type = 2304'''
+
FROM ca_contact
 +
WHERE contact_type = 2304</source>
  
 
''-- Select all Users from (ca_contacts)''
 
''-- Select all Users from (ca_contacts)''
  
−
'''SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact
+
<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type
−
WHERE contact_type = 2302'''
+
FROM ca_contact
 +
WHERE contact_type = 2302</source>
  
 
''-- Select all Vendors from (ca_contacts) ''
 
''-- Select all Vendors from (ca_contacts) ''
  
−
'''SELECT contact_uuid, last_name, first_name, contact_type FROM ca_contact
+
<source lang="sql">SELECT contact_uuid, last_name, first_name, contact_type
−
WHERE contact_type = 2303'''
+
FROM ca_contact
 +
WHERE contact_type = 2303</source>
  
  

Revision as of 20:30, 9 July 2008

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.
Retrieved from "http://greggsmith.net/wiki/index.php?title=Sample_Queries&oldid=2214"