SDU - User contributions [en]

Sample Queries

2008-11-06T07:18:59Z

David-Jhon Seward: /* Survey Results and comments query by CR Resolved Date */

[[Category:Reporting]]
{{Global Header}}
{{Global Announcement}}

== 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>

Sample Queries

2008-11-06T07:17:06Z

David-Jhon Seward: /* SQL Query for Date Fields Conversion */

[[Category:Reporting]]
{{Global Header}}
{{Global Announcement}}

== 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--"

<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>

Sample Queries

2008-11-06T07:13:41Z

David-Jhon Seward: /* SQL Query for Date Fields Conversion */

[[Category:Reporting]]
{{Global Header}}
{{Global Announcement}}

== 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'''

<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>

Sample Queries

2008-11-06T07:06:14Z

David-Jhon Seward:

[[Category:Reporting]]
{{Global Header}}
{{Global Announcement}}

== 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==='''

<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>

Sample Queries

2008-11-06T07:02:30Z

David-Jhon Seward:

[[Category:Reporting]]
{{Global Header}}
{{Global Announcement}}

== 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========'''

<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 mdbadmin.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>

Sample Queries

2008-11-06T06:59:05Z

David-Jhon Seward: /* SQL Query for Date Fields Conversion */

Sample Queries

2008-11-06T06:57:40Z

David-Jhon Seward:

[[Category:Reporting]]
{{Global Header}}
{{Global Announcement}}

== 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========'''

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 mdbadmin.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

Sample Queries

2008-11-06T06:54:30Z

David-Jhon Seward: /* 1.8 Suvery Results and comment by resolved date */

[[Category:Reporting]]
{{Global Header}}
{{Global Announcement}}

== 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========'''
""/* 1.8 Suvery Results and comment by resolved date */ ""

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 mdbadmin.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

Sample Queries

2008-11-06T06:51:32Z

David-Jhon Seward: /* Survey Results and comments query by CR Resolved Date=== */

[[Category:Reporting]]
{{Global Header}}
{{Global Announcement}}

== 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========'''
== Headline text ==

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 mdbadmin.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

Sample Queries

2008-11-06T06:49:30Z

David-Jhon Seward:

[[Category:Reporting]]
{{Global Header}}
{{Global Announcement}}

== 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========

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 mdbadmin.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