Difference between revisions of "Sample Queries"

From SDU
Jump to: navigation, search
m
(→‎Survey Results and comments query by CR Resolved Date)
 
(20 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
'''
+
[[Category:Reporting]]
−
== SQL Query for Request Area ==
+
{{Global Header}}
−
'''
+
{{Global Announcement}}
  
−
''To obtain the most current listing, from the SQL Query Analyzer run the following query on the MDB.''
+
== Examples ==
−
 
+
=== Groups with no Active Members ===
−
'''SELECT    prob_ctg.persid, prob_ctg.sym, prob_ctg.description FROM [dbo].[prob_ctg] WHERE (prob_ctg.del = 0)'''
+
<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.''
 
''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 ===
−
==SQL Query for TOP 5 Categories ==
+
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  
−
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  
 
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>
  
 +
=== 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 ==
+
=== SQL Query Reference for Contact Types ===
−
 
+
 
''-- 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>
 +
 
 +
=== 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"
−
<div align='center'><font color="red">To make corrections or additions to this article, select the ''edit'' tab above.<br>
+
    ,(cr.resolve_date + 7200)/86400 + TO_DATE('1970/01/01','YYYY/MM/DD') resolve_date
−
To discuss or ask questions about this article, select the ''discussion'' tab above.</font></div>
+
      ,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>

Latest revision as of 07:18, 6 November 2008

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.

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>

Retrieved from "http://greggsmith.net/wiki/index.php?title=Sample_Queries&oldid=3475"