Query to show requests and its associations with Problems, Changes and Projects (MSSQL)
Working on Builds: 14500 and above
Database: MSSQL
SELECT wo.WORKORDERID AS "Request ID", pim.problemid "Associated Problem ID", icm.changeid "Associated Change ID", wtp.projectid "Associated Project ID", CASE WHEN (wo.is_catalog_template) = 'false' THEN 'Incident' ELSE 'Service Request' END "Request Type",dpt.DEPTNAME AS "Department",pd.PRIORITYNAME AS "Priority", wo.TITLE AS "Subject",wodm.Dependsonid "Depends on", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Upd Time",std.STATUSNAME AS "Request Status",ohs.onholdcomments "Comments",DATEDIFF(DAY, dateadd(s, datediff(s, GETUTCDATE(), getdate()) +(wo.createdtime/1000), '1970-01-01 00:00:00'), GETDATE()) "Days since created",wos.ISOVERDUE AS "Overdue Status" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WODependency wod ON wo.workorderid=wod.workorderid
LEFT JOIN WODependencyMarking wodm ON wod.Reqdependencyid=wodm.Reqdependencyid
LEFT JOIN ONHOLDSCHEDULER ohs on ohs.workorderid=wo.workorderid
LEFT JOIN Problemtoincidentmapping pim ON wo.workorderid=pim.workorderid
LEFT JOIN Incidenttochangemapping icm ON wo.workorderid=icm.workorderid
LEFT JOIN Wotoprojects wtp ON wo.workorderid=wtp.workorderid
WHERE (wo.ISPARENT='1') AND wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth>
NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
New to ADSelfService Plus?
Related Articles
Query to show Problems, its associated incidents and change_ (MSSQL)
Tested in Build MSSQL (14306) SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency", "statdef"."STATUSNAME" AS "Problem Status", ...Query to show Problem details, timespent and its associated request IDs (MSSQL)
Tested in MSSQL build (14306) SELECT "prob"."PROBLEMID" AS "Problem ID", "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status", "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...Query to show Problem - Task Report with Problem Details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Attached the sample query output. Query: SELECT prob.PROBLEMID AS "Problem ID", prob.TITLE AS "Title",catadef.CATEGORYNAME AS "Category", longtodate(prob.CLOSEDTIME) AS "Closed Date", ...Query to show total requests and changes with its total worklog hours of a technician- MSSQL
Working ON Builds: 14500 MSSQL: Requests: SELECT rctd.first_name "Technician", count(wo.workorderid) "Sum of tickets handled by him", ...Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...