Query to show Request and associated task details (PGSQL & MSSQL)
Tested in build PGSQL (14300) and MSSQL (14306)
PGSQL & MSSQL:
SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", ti.FIRST_NAME AS "Technician", qd.QUEUENAME AS "Group", std.STATUSNAME AS "Request Status", longtodate(wo.CREATEDTIME) AS "Created Time", longtodate(wo.DUEBYTIME) AS "DueBy Time", rcode.NAME AS "Request Closure Code", wos.CLOSURECOMMENTS AS "Request Closure Comments", sdo.NAME AS "Site", ad.ORG_NAME AS "Account", tk.taskid "Task ID", tk.title "Task Title", taskowner.FIRST_NAME AS "Owner", tk.PER_OF_COMPLETION "Percentage Of Completion", LONGTODATE(tk.CREATEDDATE) "Created Date", LONGTODATE(tk.SCHEDULEDSTARTTIME) "Scheduled Start Time", LONGTODATE(tk.SCHEDULEDENDTIME) "Scheduled End Time", taskprior.PRIORITYNAME "Task Priority", taskstatus.STATUSNAME "Task Status", tasktype.TASKTYPENAME "Task Type" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN RequestClosureCode rcode ON wos.CLOSURECODEID=rcode.CLOSURECODEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID LEFT JOIN workordertotaskdetails wotc ON wotc.workorderid=wo.workorderid LEFT JOIN taskdetails tk on tk.taskid=wotc.taskid LEFT JOIN taskdescription tkd on tkd.taskid=tk.taskid LEFT JOIN Aaauser aau2 ON tk.createdby=aau2.user_id LEFT JOIN Sduser sdu2 ON aau2.user_id=sdu2.userid LEFT JOIN SDUser taskownersdu ON tk.OWNERID=taskownersdu.USERID LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID LEFT JOIN PriorityDefinition taskprior ON tk.PRIORITYID=taskprior.PRIORITYID LEFT JOIN StatusDefinition taskstatus ON tk.STATUSID=taskstatus.STATUSID LEFT JOIN TaskTypeDefinition tasktype ON tk.TASKTYPEID=tasktype.TASKTYPEID
With Task Order:
SELECT ad.ORG_NAME AS "Account", wo.TITLE AS " Request Title", tk.taskindex "Task Order", tk.title "Task Title", taskowner.FIRST_NAME AS "Owner", taskstatus.STATUSNAME "Task Status", tk.PER_OF_COMPLETION "Percentage Of Completion", tk.ISOVERDUE AS "Overdue Status", wo.WORKORDERID AS "Request ID" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN RequestClosureCode rcode ON wos.CLOSURECODEID=rcode.CLOSURECODEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID LEFT JOIN workordertotaskdetails wotc ON wotc.workorderid=wo.workorderid LEFT JOIN taskdetails tk on tk.taskid=wotc.taskid LEFT JOIN taskdescription tkd on tkd.taskid=tk.taskid LEFT JOIN Aaauser aau2 ON tk.createdby=aau2.user_id LEFT JOIN Sduser sdu2 ON aau2.user_id=sdu2.userid LEFT JOIN SDUser taskownersdu ON tk.OWNERID=taskownersdu.USERID LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID LEFT JOIN PriorityDefinition taskprior ON tk.PRIORITYID=taskprior.PRIORITYID LEFT JOIN StatusDefinition taskstatus ON tk.STATUSID=taskstatus.STATUSID LEFT JOIN TaskTypeDefinition tasktype ON tk.TASKTYPEID=tasktype.TASKTYPEID WHERE wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth> ORDER BY 9
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 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 ...Query for request attachment details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician", sa.ATTACHMENTNAME "Attachment ...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 request details along with technician's and requester's department (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Along with technician's department: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS ...